FOR XML PATH实现小九九

时间:2023-03-10 06:36:51
FOR XML PATH实现小九九

数据库环境:SQL SERVER2008R2

今天我们用SQL实现一下九九乘法表的功能。

实现的逻辑不是很复杂,难点在于怎么把想要的内容从同一列里头拼接到同一行上。

在这里,我们用到了FOR XML PATH,FOR XML的用法,可以到MSDN去看,这里就不细讲了。

/*数据准备,生成自然数1-9*/
WITH x0
AS ( SELECT id
FROM t100
WHERE id <= 9
),/*生成所有可能的乘法组合*/
x1
AS ( SELECT a.id AS aid ,
b.id AS bid ,
CONVERT(VARCHAR(1), a.id) + ' * '
+ CONVERT(VARCHAR(1), b.id) + ' = '
+ CONVERT(VARCHAR(2), a.id * b.id) AS result
FROM x0 a ,
x0 b
WHERE a.id <= b.id
)
/*把被乘数相同的转到同一行上*/
SELECT result
FROM ( SELECT bid ,
( SELECT ' ' + result AS [text()]
FROM x1 AS t2
WHERE t2.bid = t1.bid
FOR
XML PATH('')
) AS result
FROM x1 AS t1
GROUP BY bid
) t;

相关的说明已经在代码里进行注释。最后,附上结果图。

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAhUAAADBCAIAAAD3ttU5AAAWT0lEQVR4nO2dwY3rOBZFFc/seunKoDPoBOwJYnYdgFEB9EQwmJUBTyoGnEAvf6/7A56FbYmSSOq9S1JWlc8BUShUieQ1KelKlPxed7vdbrfbf/57+tmSP//6u2n7AACwAn/+9fftSYd/AACAEfwDAAAU8A8AAFD44v5xOe52x0u15gAAwMq38A9cBABgdfAPAABQ2IB/XI673X6/67puf7ocd92dhyH0f9gdL6PVqsE57nX7GgAAsAbb8I/HuT/wh8evp323Pz222Z/i/sH9BwDAC9iGf/T20YXcnWF8M4J/AABsg635xz7a92n/XN3CPwAAtsGW/CM0kH7BarS09VzO+vnz52nf4R8AAK9jU/7x83GnkXp+Hi5x7XaBc9xrxe9dAACgBRvwDwAA+ILgHwAAoIB/AACAAv4BAAAKcf/486+/KRQKhULJl4h//GhM13X85Cc/+cnPL/3zFjDyj3a3PD9+/Pjf9UahUCiUr1u6rnuNf3Rd9/IPT6FQKBS5cP9BoVAoFKVw/0GhUCgUpQj3H0EEqgL/cAo9/9odfp///Y/DP/51dWyv9UKhUCiUWXHdf/Shpyr4h/n+4/rPX+6dTs7s/d+77pfPfy9vr/VCoVAolHj5yvcffxy6386//9b9+odte60XCoVCocSK8Pyjjn84n394z+z4B4VCobQtX/n+w7v9H4cuoN5dC4VCobxj4f6jvBaFQqG8Y+H+o6QXCoVCed/C/Ud5LQqFQnnH8k73H61qUSgUyjsWvn9OoVAoFKUQ/4pCoVAoSuH+g0KhUChK4f6DQqFQKErJ3X+0y3fI/QeFQqF89ZK7/2jNFpIv8pOf/OQnP+Wfr1m/ugEAwFfmx6uef7ziwwIAQDXwDwAAUMA/AABAwecfp333oCyCid8/zofucI79+ePzOt/0QaxGpoPFWrlt6inp684bBADYDg7/uBx3T9sIfm3uH9fPj+g5uP97F55or58fzw2DXy19LNVKb1NTSdgk/gEAW0Zcv7ocd7vjZRX/uBO7/zgfusM5cWNyu91u188P4RRsqTXdprKS6+fHx+GgiAcAWA3NP9a8/7iTOTmn8F/1W2t5W3Zufz50h7NmfgAAq6H4x+W460ruPlbxj+vnx3QB6DzKH5VcpFo6bVu2KVDy+KT4BwBsHLd/lJvHCv7hPcXba1Uwj6Xt75vjHwCwcXz+UcU8WvtH8pSdvf9Y1TySSoJH8dPn8QAA28LjH6d96Xu7K/jH+SC+LbtYy9uypuQJ9x8AsHEc/jF8+aPruq7oPqSdf4yv7K3X75Za3pY1JT34BwBsHL5/DgAACvgHAAAo4B8AAKCAfwAAgELOP5rmr33FhwUAgGrk/AMAACBD0j9YvwIAgBT4BwAAKOAfAACggH8AAICCzz/6ECaFYbD8/qHkr3UFn7LUkvPX+sJgGarlNll3TADgbfH4x2n/DHp12pc5iMc/fPlrg7zhnpDvhlre/LWikiDsYjIFerLltccEAN4Zbf2qMH3tOvlr26Wv9eav9ShJmoat5XpKwk2vnx8YCABMkPyjNH3tKvlr26Wv9Tbt2dzXdNNEuoOTaem4AOCb4/aPy3G3oecfCe7rONMKS/lr47WijZvPpl4l18+Pj8MhvmInqZWVBP/8OBwIJg8AU+T1qyILWeP+o80NiHQp7lAStG+p1fQGZMC3qAYA74H4/m7hA5BV/CP2lCJ7/xGvNf6Xdh51KAnenrI8rPA+5JHGRHQdAPjeOPwjuOnY7v1H+i2p4lrOfLSakuCTJmt5Wy4dk7JEvADwXRG//1H09lXj+4/+ktp1Yb5YS8hHqylxff/Dm6BXGxPcAwDm8P1zAABQwD8AAEAB/wAAAAX8AwAAFHL+Qf5aAABIkfMPAACADEn/YP0KAABS4B8AAKCAfwAAgAL+AQAACop/DHkI1/MPR/7avoIQ6jBfy5u/VlPSJxG0KDHmr9WUPCsNneRGwNJXS22EWwFYGb9/XI67VeNfOfPXBv+U4qwna7nz12pKRmELE/W8+Ws1JbORd8RhjPTVVpsp7y8AVMXrH5fjbrffl2WvbZ6/9pGEyXkScdQy56/1KwmaNsSMtOev1cYkqSIbNz7RV1NtmAbA6jj947Tv9qfS7Oet83+cD93h7M5+7qhljoKuKOk3t9mHcVi0Melrujr29lVDGylKANbH5R+nfbc/FSePauwfjy0j56NcrqR0rRnmLFKakn5tZqEPT/5aVUlQd9p1Up1jJCtqs+f9BYBaOPyjt40t+0d/GnJdz9pr2VMQakqCra1eZskfpSh5MB35/Ah4+6qlzZn3FwAqYPePy3EXXg4WPUJv5h/BQ9r5k9rk9Wy21qR160lOUzI6jVbKXysqCbYY/rw0AtaRrK/NmfcXAMpR3t/d8v1Hj3YSydVS07j6lAzPgavlrxWVDIKCZ9Tm3koTs3u1GfL+AkBd8A9rLSF/rabE8h0LLR9t4TnaNQKr+wff/wBYG75/DgAACvgHAAAo4B8AAKCAfwAAgELOP8hfCwAAKXL+AQAAkCHpH6xfAQBACvwDAAAU8A8AAFDAPwAAQMHlH6f9EL2iKIKJ3z/s+WvDKBv2iBimWs78tZqSvq4va6wzo20tbY52zBltRW3zUTKMW7avhtpKxh9gK3j9Y3/SXUPzD2/+WnekLGMtf/5aTcns81oiursz2lbSZm/HkdFW0RaZHVMk/FRfjbWJ4w+wLTz+UR43UfGPO+b8tfXj7ho2r6gkbDH3q1VaW23WdjwZbYvDrz8asIzbkrO00kaIefgWOP3jeU223fi7qQvHmrVsEcI1JQ/GmfXsmS2MwcsrabO248pQW6Tt1g+Bady8fVXSVtwOwCbw+Mdp//SNy3FXtJLV0D8y+TMy+YgMWTd6rFmkNCXDFm7/cGS0raTNNm7OjLZF2obZMY2bZ94ranP3C7BJxPevCpeyGvpHQIulLE8KwhIl6v2H/3xUos3STkmGWiGJSD87DTI5VtNW0g7Adngn/zBcIUZqjf8lL1uUZGb1PP8oPg86M9ou9evNaKtrm87OlsYts+fgH/B1cfhHsGg1rGRtzT+Cy+/4+6J6LWf+Wk1J0Jnj/StvRtta2lztGE+Uorb57PjGzdRXLW1l4w+wFXz3H/0XQLb7/FzN7bpYS8hfqyl5VhW//2Hsq7q2igtEgrbo7Gxk3PLacA/4uvD9cwAAUMA/AABAAf8AAAAF/AMAABRy/kH+WgAASJHzDwAAgAxJ/2D9CgAAUuAfAACggH8AAIAC/gEAAApO/+gDmJTlIfT7hz1/bT6Va7YHc6wLW/5aVcmjZtP8tdW09fERF+NwmPPXitpSo5SNMOXNX1uqLZL4N9NOpq+W2gr2DXgvXP5x2j+NozCAosc/hPy1j021EHepWlL+WkHJ9POaYiMq+WsraAsULTXjy1/r1pYcpUi/IUL+Wv+4zWfHmls32ldjbeK+Ae+Ixz9Ko+5q/nEndpxHM4xqu7yzlil/bdHB58uxkdukqbagt2yES0/+2uKTViAq0W9s0ylNtD37s8SWz/TVWhumATYc/lGadLC6f8TQ0rk5a3ljpAtEP6+hSXtq3Tra+hNwdoLOjvy1xQn5xsHR8/0688iWagvS1y7mtvL2VVEby1ZgxOcfu/3+kQJ9I88/ZjwuOKMrXukMP7lasS6MSdIFJcEW0z8v9mvPX1tT2+MUnFF2duWvLdMWjpKh3+Fa23pNIGsLZ8foH/Z9srI2T7/wzjjvPzaf/zw4fTgupOy17CkINSVPpp/X3K/53qiKNluOJlf+2hJt0fy1Dt8ypDApy1v+qGW9/3B6Wy1t5GYHI77nH/3jj+3mrw3eS3FkGM3UCrCbh65k2GL8DNjzjHt520raDOfBYIWoW7hPKdQ2HiV3v+3HLahle/5h2SdbaPP1C2+M9/2r+13Hdu8/gi1dV0+GWsNrKfXaXK5r6tf0jlYjbeZrVfPJSNKWHqWFC4LhUYlFnaItMjvW969cfVXSVrRvwHvxxb//kdpWWL1dqjW+hrPdD9T4joWx334zx4ubxdpG8gzPjdppy4xSvt91xm3eS+n3P1pq4/sfYITvnwMAgAL+AQAACvgHAAAo4B8AAKCQ8w/y1wIAQIqcfwAAAGRI+gfrVwAAkAL/AAAABfwDAAAU8A8AAFBw+MfluBvFiCiIYeL3j0T8klkWz2nYPHvUXkOtXMyJSkqCrqbb5yM05UJO1NOWGAFTdJm4/pba+qay+Qd7Zls10BYGNUv2m67WQlvflT22ykJfq2gjtMoWUO8/LsddQQgsj3/0+14k3OrCAXj9/BD2skStdIzCukoSn/eazcMayUJaX1tsBFKzE/9YYwmNtQ1/Wsrkl5Jba07jOWIXK6fyyDbY84PZccfiHPXVVlt6P4fXIPpHEMq9tX/cMeevHf1fCT5tqRWJj11ZydwGsnlY09IajdK4m8WDOaG/qbZAYrKLpei3tbU9+7PEkvTmr9W1Jfcum8ym+9tI2yRfAAbyciT/KMz+0Tr+7h0td4GpljOutaJk/Hnt+V+9IbfFDA+TbpZmx5O/tp62vn5anuFyuaa2+W1b9jJdvML2akvOjkGCt69CbeN8AeQmeTmKf5StXa3kH5HLE1v208VuvHuupCT8vNb8r1dz/toybfMRyM+OL39tTW3Xpdy6zvy1JdpGs2Po96rmkXVqS86OZT8X1qIKtT03N96OQ1sE/+izSG3ZP7Tl0eVa/sueUiX9wWM7/7rOg4q22Ajk2nHqr6fNlKMporORtl7g/DletN/gszSc09Ts2Pbztkdlfs8xJvyCprj9o3jtag3/iB+ThivE5Qt85z6rKZnkgAtZ7N9+HhS0JUYgMztu/bW0TdbKXztumbrJfqU8sk5t8dkx7ud11q6c2sL/8vzj5Xj9o4p9tPaPNo8+0hlSayu5RT9vpi33OzOatuQImGbH018NbbY1ovmKUnVt89mx9SvkkZX3t6CmdT9v/egjUnMYCOVghPo4/aPC2lV7/2izdmXMI1tBSaJm/vhT8rA6taVHoLZ/VNJmyRG7wrhFezH16/2yQ8FLreHLBqb9fJ0V5bG2m39IoCl8/xwAABTwDwAAUMA/AABAAf8AAACFnH+QvxYAAFLk/AMAACBD0j9YvwIAgBT4BwAAKOAfAACggH8AAICCzz/6FLaFMbD8/pGIfhBLRtPHXfNG51mslYuHUU/Js6txfg1zHI7IJvW0pUbAEj0qvk0DbWHg+6VxC6N1zHqrPKejEbDEVslNaktti+1MQxvO9oZK2mKzYzgWcn1VHLeEkrcKDOzxj8tx94h+ddqXOYjHP/rJjYRbjRz4oxBrnnh9S7XSMQqrKol83iFWXLKhszN/raQtOQLXbG7d5DZVx202AraYkqmoTHXndGhyFlE+ExUsNe/ttbnauRry14ra5rNjOBaSfVXXFqtmORa+EU7/eLpGYRzFOvcf51imzOB4dERsc9aaHvQVlYQt9r869sfZ+ai+tkkDV0Nu3cQ2TbRFzsjJk3T+7F1T23gExtMbb2cpMm8rbc52TPlri4/KoXFPIP5pXxW1xZVYjoVvhWv9qjeQ0jC81davIoSBRIWQ70b7sDStKbkzVPElOrBuXaJt3M3ZkJvWsk0tbZERSA9K4pK0vrbxCFhyk/gTXNTR5mvHOqmSttns2MbE25eiLa7Et59/B5zPzx8PQEpTgLT0j363iyxnp5ds07WizRt3EE3JY4vBP4x5TK+u/LW6tnAEhsWi9JBYtqmjLToCufk6+/PXKtqmI2D0D3f+2hracu3EOmy4v81mxzomZv2ytpgS/37+9dHWr0qzSDX0D9PKsl7LZx6Ckgfj+w/HOc55iebXFo5A9ISY6sp9qSoeiaMRsM+XqTNJ23wErPcfLm+rpM3TTuOjctaAaUy8fanjFvE2137+LXD4R2gahQbSzj9yR2b+amLpeL65zENVEmwxLJBbtEX7ra5tPALB+kKXut6zbFNH27wFeb6qaouNgO35hzzvJdqMx4JRVYG2WAuGMbHrL9I2VXLW8jR/dTz3H8NbV8ObWFvzD2VFwlhreOGiXpu5yuH7V/ffk+0E/7D1JY/S4jVfGuvpRtIWGQHDfAW1bG8pFM2p9xp/ed6baLO247nOLp3Tvr5hTLx9ieOWVML9R/L5x2n/tNeyLLZNn3+cLW/W+2uNr0VM1xeakmfVoM55+Z33fhPjvitoy4xATf8onsH+TGOZrxXGrSccAVM7hnl/pbaqx1e+1jA7nmOh7bgllOAffP8cAAAWwD8AAEAB/wAAAAX8AwAAFHL+Qf5aAABIkfMPAACADEn/YP0KAABS4B8AAKCAfwAAgAL+AQAACj7/6OOXlIUvaZu/1hn0wVErF+egnpJn1aFSH+UuExch11eDUbpXm4bfyzYWjzDVdAaXGwqjnETCd1ed03AEsv1aOqumLakkExFsYd4rjtusmqWd3PFSSVt0BCxxUNY5ThMjoCX7yeHxjyF+4pr5o/qhiISCjez252keUxOGWukYhVWVzD/v0Fv6iE72VVPbQpTGazYXxNWcv7baDA4BFLMDl4xIWW9OhyYD/1isntJfV1ti48h8Jbga8tfq2sYjYGknebw0mNO+4cPZFMN0leM0NgKps2gpDv+YxG9/ffzdcyQb5SQasz3Sp6tWJM5zJSWjFmcNGXaiWfS2+tqi3UQvnkabG/PXFmt7NmCJqLsU/baetvEIWGLsZfRX1BZXkpivuJZm+1tuBtMjmDle2hwL8z3fGFu+lbbkCGzl/uNy3BXlIKy2fhXdMIjGbA2E6aulxIV2huQMP2+/O9jswzJQRdpi3eRPiGdPXs9CbU9pppFIXPbV1zYZAUO/7iDsmraoEvt8GSdV0pYbgYX/mY8XVVukN4s4b1+ittQIvNY/hucfu/1+o/mjbsOK4ewCKlzpnd3IJWvNsM+kpuS5xXyFbKHXqyd/bYG2yAhkL47O3ryesrZwBK6WXKfj49MydJK22QgY+jXpL9cWUeKYL/tFsaAtNQLL+7nteCnRFnYVWVhffqRlv72TtMVH4NX+ERrJZv0jrOO/sl6opV2u+5VE16+MR7R62WojNgK52elF2/1D1tYLPJxDndZbEVdfdm35Ecisdcj5oLQ9//rMEWuer7ZHZXYE0mPiO15EbUGN6X24/eKy0f6WHoGt+MeG8w8OzPYww9XE8k2zcFwqSobPO1kDLVnSL9cWHYFsj8H6SOe5JvRrm+ppkPdX0rYwAsl+nfolbdNqrjyswgWBT1t2BIzPGJqO26R977qh8/mZVVt6BF7qH4NpnPaFb/C2849hmId3NyrV8jQoKwk6e1axrXUsv/tRri2+sfUgNW5XOoOzdRjbuJku78rmdHQjYuh3WX+5toySpflynJzVcZuOgGk/d65JFszp7OS8VN3bl36cxkfg1fcfm/v+R2LT/HWWVmvs9778tf5JG33eoevsQwbXBb6gLT4C5pnxP/8QZ3D0HHgD49YTjoCpX2dn1cZtpjZezdONOG6zapZxsxwvdbSNz82W84O3r8I5nd2zbGL9qhS+fw4A8NXBPwAAQAH/AAAABfwDAAAUcv5B/loAAEiR8w8AAIAUEzsZ/GMF7wIAgG8D/gEAAAr4BwAAKPwf2cSaNORIemoAAAAASUVORK5CYII=" alt="" />

(本文完)