sql 小技巧

2023-06-20,

 declare @pids varchar(max)=''
select @pids=convert(varchar(10),pid)+','+@pids from product where pname like '%red%'
select @pids

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAooAAABZCAIAAAA2I5JzAAAMmElEQVR4nO2dS2/bxhqG5z8pCZL+F8OwXfCHBAhQt150YwPceREYMI6bOj5A1l4GEQGjPW1dNNDpxb34KDYiyRfJkgydxUg0yZmhOOSQppXnwQuCGg3n03CGfPWRtCz6/X6/39/e3u4AAABAPRBRe17f2Hyguu/dCAAA4JKkPU8eINgzAAAsGAtiz/XXfQ80AAA8JBbEnsf1BnsGAAArstqz5wdaNbykqjRmyfrGZi/O4eHh4eHhQZzt7Ze9+wB7BgAAWyzs2Q8mU/kTz6B0exZCpLx0aM++n/wY7Xbb833sGQAAHgT5s2c1b254QaNRyJ6FmXR77sYJ7bnb7QZnk+BssvOu43l+9z4YjUbYMwAAWJHVnqP5aLPZDNcPDw/lymAw8P2g0fDmenOK+0Zt2LSutedEr+SnPT4+DoKg2WzuvOv4fuB5fqE9JUSOtzqdDvYMAAC25LHn0JInk8nBwYH0ZpmzPnv2bK43p5hu7uz5YxzfD/wgODg48P1ge/ullOf5HzMjhMiyYiLaFPYMAAC2ZLXn7e2Xx8fHzWbzMI60QPngVbo9qy6rtee561p7Po+j3nveedeZptFB4AdBq9U6n4cQIroSXSYqmDaUDIdD7BkAAKywsOdwPZo9+34g77AGZ5Pt7Zcme064rCkzzp09n8Xx/eDk5KTdbrfb7SCY7Lzr7LzrRD9zo+G1Wq0zG4QQaomJaDXsGQAAbCl0cXswGBwcHISPX6Xbc7rFFqm/vrH5IY7vB61WK5biB4HsxsT3fD9oNpvSoT/oEEJE11XUaqbNP3z4gD0DAIAtWe15a2srCALpduFtXbkM7+/6vj/34rY0XW1mnJKMpifQ6xubrTjqxe2pPU8mk8mk1Wo1PK/R8KRDaxFCJFa0b5mI1r++vsaeAQDACgt7vrM6P5DPgnW73e3tl8HZRF5A3traSnlyO+HW2vVoSXSZzvrG5vs40p5lWt9ut4OzmT373sTzJpOJ5zd8P2h43vt5aN03fMu0SfQl9gwAALbksWd5QTu83xze3J1rz9Hs2WTPQnnGe65Dr29s/hIntGfpzcFZ7OJ8t9tttVp+EDQa3i/zEEKYSlKy52h97BkAAGyxsOfwOe3oBW35wPbWDJM9J65Oa69aq3VMm6v2/HMcacahNwfBzJ49b+I1Tk5OPL8h7flnM0IIuVQxVdZydXWFPQMAgBUW9vzs2bNGw0ssoysSUwsJt9aum+qks76x+VOcmD0HEy9ohE+et9vt4+Pjw8ND+fz2TwaEEIkV01tqzcQm2DMAANiyIP+x6oc48tls+eMkUlN79hoTr9FsNhteQ9rzDzqEENr1RIlcSVRQCy8vL7FnAACwYkHs+fs48rGv5eXlhudJSXue/izJ7OJ8o+F9Pw8hROKlLImWJy59J97FngEAwJYFsefv5iHNWNXcDYtzcXGBPQMAgBULYs9HNQZ7BgAAWxbEnhFCCKFF0iLYMwAAwIJhtOd/ffNvhBBCCN2L0uy51O8FvV6v1PYBAAAqxqG1Yc8AAABuWAR77na7pbYPAABQMQ6tbTHtWf1B0Ow/EQoAAJCPBbbnN57w3qT+7+fp5zaUh+8m2jXY8zQcAABAcRbBnjudjlr4/uvPVLdMSXxNrqyWG+0cgwYAAEdorS0f92bPHz9+VMr0VlnQnrWbRwq1XwkAAACs0VlbTtLseTQa9wfDMjQe3/5z+r9k+68/F2sHamUhhKmdaDYcrZlYSbSQeLm/JtZel9JNhBBCn46ktVVhz8PR+Lp/U4bG49u//v4n0f5/vnr69Kufrvs3KTeeJbJ+uBKua99KVFDfCuMihBBCuSWt7WHb8+3t7Z9//W2y56gSvmsqz2LPamFKXIQQQshK0toqsefh6Op6UIbGt7cnf/6daP/7L58+/fLHRE0hRLjUKlohrBatn5KFp8RFCCGErCStbQHt+Wp/Tay+jpZo7VaVWi106xR3Dwu/XRWr+/c/rgghhB60qrPnm+Ho8qpfhsbj29//+FNpf39VrL2avRRCXM5bV1/a1/lh/eldUIQQQiifpLVVYc+Dm+HF5XUZGo9vf/v9RG3/u/WnK6+uLy6vhRDR8uhL07qpvnyp5eLy+uLVqljdL6mbCCGEPh1Ja6vEngfD3sVVGRqPx7/+9oeu/W9XxKoQQt0kaqthSUq1RKG2pgy3V04fEUIIfVKS1laFPfcHN93eZRkajcf//fX38tpHCCGEKpa0tirs+UcAAACwoQp7HpXJcDgstX0AAICKGQ6HVdhzp2SEECxZsmTJkuXCLF1583x7dhgpQafTOT3vIYQQQoshYf4HTjm4T3sWQtz73kQIIYSciOwZIYQQqp3InhFCCKHaqQ7Z8xtPeG+KBTZlz29fPBExVnamb+0t3a33dpZn7y/vzd4NefI8iNVZ2nUS4q780YujRJ0HFEJtsLwQssTcjrMQtr3It1vCpsLRcR7CeS/Uz+y8F2qDzntxuruSqKMpcR5CmYfFQiTPUbbHRY4QKSeZ4pM25WAsOhbJPa/pV2W9sJK41+z5/defyR44sGcxN3sOvni0vHd6fvT88d2un5Y//uLtee/0/Oj5YzlaylG0uyKmdTQHWK4Qs/LwNPQQQ6gNOg9h6lesHdchrHqRb7cEXzyatry3JJR+OQnhvBfqZ3beC7VB570431uanVhn31zVEuchlHnoIMTc48txiLcvnszM5uj54+S3pRwhNA2mH+95eqHu+dRdl2/SWp21MmvBs+eodpajuykyQrsr0a888SlyNy+j6YJpXlqEmI3c0vK05QpCRJQ2aSxDmBp0FCItaKKdMkJk6IWyuTqUmsGNzDG1X25CuO6F5jO77kVqg456cXd6nUktcR5CnYfudtS8yV9KiGj9/CHUBlOP9wIhIns+tV85h9vmrJVdlWbPo9G4PxgqOlgTn+9ryi00P3tODsndaCV296MXRzLjlCh559Hzx4bv11YhzqdDGHtZdohoO6bThG0IU4OOQswJajrMHIaY1wvN5upQagbXfKnGWQjXvdB8Zte9SGnQUS8iGVsvrDn/CmSxEOo8LLqj1HOUzXFRNERK9mw33MkG0w7GQiHi9mzsV87htjil2KjS7Hk4Gl/3bxS9XhWf72vKLTQ3e1a+rKW6TnwAwotU8l6FLhm1DxG5vKPcYysxxOns/orpC51lCH2DDkOkTPSUawxOQmTshXZzdSg1gzs9TRgTHQchXPdC85ld90LboMNevH3x5NHyyqPIfUG1xHkIdR4W3VG6c1T246J4CNP1BuvhjjeYxdhyhYhfPzP0K/9wl2PPlWbPpdpzavasHhXZkkJdieHulF2IsEQ7eOWHMM3L3DtKbdBNCHPQlDOdqxCZepE+f9ShnJbMvztVOITzXuS5x1Y4hOteRKzlLmObd74uGsJq0toOd47jokiIFG/OFyLaYAZjy9cL/W5xEqI8e642ex6Orq4Hil6virVvNeUWSs+edTso4y3VrOdryxDh0wraaywVhNBPGqc7ylEIQ9CUSe8qRNZezLl1rS+ZewAXD+G8FzlOQ8VDuB+L3ZVkCLXEeQh1Hjob7kwZp6sQqd6cJ0SywXl3bfP2wsKecwz3Itx7LtWezdmz9nu99kmBac23d7eOpo+P3pXsruievbQOoZ/3lYXQfwMosqMiiYjTEIZ+pTwl5CCEXS9SNp8NpWZw51xhcxDCeS80n9l1L9QG3fci/kzv0q62xHkIZR66Gu7YM/OZj4t8IfSnpmLDrT5hkHY9JsdYJPe8YdcVCLEYT27fDEeXV31F+6ti7ZWm3EJp2XPki0xEWf8QNvwapfujQE1Clj3E6Wyu3EuIWUnRECk7ynmI2F+pmm7pOQph1YuCf5QcOVmUFcJ5L8y7rsQQrnqR4e+eSwiRnIfOdpQ21SsjxN0md4WFQugadD+jUs4A93FcZPvTuPPeacXZ8+BmeHF5XYbu41fD9pYyPJ1PCEIQghCE+ORD5FGl2fNgMOxdXJUhfnMbIYTQIqnS7Lk/uOn2LssQv7mNEEJokVRp9vxjaZA9I4QQWiRVmj2PSoPsGSGE0CKp0uy5bIQQLFmyZMmS5WIsK7Jnh2EAAAAgO9gzAABA7cCeAQAAagf2DAAAUDuwZwAAgNqBPQMAANQO7BkAAKB2YM8AAAC1A3sGAACoHdgzAABA7cCeAQAAagf2DAAAUDuwZwAAgNqBPQMAANQO7BkAAKB2YM8AAAC1A3sGAACoHdgzAABA7cCeAQAAagf2DAAAUDuwZwAAgNqBPQMAANQO7BkAAKB2YM8AAAC1A3sGAACoHdgzAABA7fg/7RHhE/umOE0AAAAASUVORK5CYII=" alt="" />

将表product里 pname是红色的产品全部累加

我们要慎用@@IDENTITY,原因是 @@IDENTITY 它总是获取最后一条变更数据的自增字段的值,
 而忽略了进行变更操作所在的范围
约束。比如,我有表 A 和表 B 两个表,现在我在表 A 上定义了一个Insert触发器,当在表 A 中插入一条数据时,自动在表 B
也插入一条数据。此时,大家注意,有两个原子操作:在A中插入一条数据, 接着在B中随后插入一条数据。
 
   现在我们想下,假设上面
表 A 和表 B 都有IDENTITY自增域,那么我们在表 A 插入一条数据后,使用了 SELECT @@IDENTITY 输出时,输出的到底是
A 还是 B 的自增域的值呢?  答案很明显,是谁最后插入就输出谁,那么就是 B 了。于是,我本意是想得到 A 的自增域值,结果得到了 B
的自增域值,一只 BUG 随之诞生,搞不好还会影响到整个系统数据的混乱。
 
   因此,对于这种情况,建议大家慎用 @@IDENTITY,而尽量采用 SCOPE_IDENTITY() 函数替换之。SCOPE_IDENTITY() 也是得到最后一条自增域的值,但是它是仅限在一个操作范围之内,而不@@IDENTITY 是取全局操作的最后一步操作所产生的自增域的值的。

sql 小技巧的相关教程结束。

《sql 小技巧.doc》

下载本文的Word格式文档,以方便收藏与打印。