* \u8bf4\u660e\uff1a\u590d\u5236\u8868(\u53ea\u590d\u5236\u7ed3\u6784,\u6e90\u8868\u540d\uff1aa \u65b0\u8868\u540d\uff1ab)<\/p>\n
\r\nselect * into b from a where 1<>1\r\n<\/pre>\n* \u8bf4\u660e\uff1a\u62f7\u8d1d\u8868(\u62f7\u8d1d\u6570\u636e,\u6e90\u8868\u540d\uff1aa \u76ee\u6807\u8868\u540d\uff1ab)<\/p>\n
\r\ninsert into b(a, b, c) select d,e,f from b;\r\n<\/pre>\n* \u8bf4\u660e\uff1a\u663e\u793a\u6587\u7ae0\u3001\u63d0\u4ea4\u4eba\u548c\u6700\u540e\u56de\u590d\u65f6\u95f4<\/p>\n
\r\nselect a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b\r\n<\/pre>\n* \u8bf4\u660e\uff1a\u5916\u8fde\u63a5\u67e5\u8be2(\u8868\u540d1\uff1aa \u8868\u540d2\uff1ab)<\/p>\n
\r\nselect a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c\r\n<\/pre>\n* \u8bf4\u660e\uff1a\u65e5\u7a0b\u5b89\u6392\u63d0\u524d\u4e94\u5206\u949f\u63d0\u9192<\/p>\n
\r\nselect * from \u65e5\u7a0b\u5b89\u6392 where datediff(\u2018minute\u2019,f\u5f00\u59cb\u65f6\u95f4,getdate())>5\r\n<\/pre>\n* \u8bf4\u660e\uff1a\u4e24\u5f20\u5173\u8054\u8868\uff0c\u5220\u9664\u4e3b\u8868\u4e2d\u5df2\u7ecf\u5728\u526f\u8868\u4e2d\u6ca1\u6709\u7684\u4fe1\u606f<\/p>\n
\r\ndelete from info where not exists ( select * from infobz where info.infid=infobz.infid )\r\n\r\nSELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE\r\n\r\nFROM TABLE1,\r\n\r\n(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE\r\n\r\nFROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND\r\n\r\nFROM TABLE2\r\n\r\nWHERE TO_CHAR(UPD_DATE,\u2019YYYY\/MM\u2019) = TO_CHAR(SYSDATE, \u2018YYYY\/MM\u2019)) X,\r\n\r\n(SELECT NUM, UPD_DATE, STOCK_ONHAND\r\n\r\nFROM TABLE2\r\n\r\nWHERE TO_CHAR(UPD_DATE,\u2019YYYY\/MM\u2019) =\r\n\r\nTO_CHAR(TO_DATE(TO_CHAR(SYSDATE, \u2018YYYY\/MM\u2019) || \u2018\/01\u2032,\u2019YYYY\/MM\/DD\u2019) \u2013 1, \u2018YYYY\/MM\u2019) ) Y,\r\n\r\nWHERE X.NUM = Y.NUM \uff08+\uff09\r\n\r\nAND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B\r\n\r\nWHERE A.NUM = B.NUM\r\n<\/pre>\n* \u8bf4\u660e\uff1a<\/p>\n
\r\nselect * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and \u7cfb\u540d\u79f0=\u2019"&strdepartmentname&"\u2019 and \u4e13\u4e1a\u540d\u79f0=\u2019"&strprofessionname&"\u2019 order by \u6027\u522b,\u751f\u6e90\u5730,\u9ad8\u8003\u603b\u6210\u7ee9\r\n<\/pre>\n* \u4ece\u6570\u636e\u5e93\u4e2d\u53bb\u4e00\u5e74\u7684\u5404\u5355\u4f4d\u7535\u8bdd\u8d39\u7edf\u8ba1(\u7535\u8bdd\u8d39\u5b9a\u989d\u8d3a\u7535\u5316\u80a5\u6e05\u5355\u4e24\u4e2a\u8868\u6765\u6e90\uff09<\/p>\n
\r\nSELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, \u2018yyyy\u2019) AS telyear,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201901\u2032, a.factration)) AS JAN,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201902\u2032, a.factration)) AS FRI,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201903\u2032, a.factration)) AS MAR,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201904\u2032, a.factration)) AS APR,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201905\u2032, a.factration)) AS MAY,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201906\u2032, a.factration)) AS JUE,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201907\u2032, a.factration)) AS JUL,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201908\u2032, a.factration)) AS AGU,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201909\u2032, a.factration)) AS SEP,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201910\u2032, a.factration)) AS OCT,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201911\u2032, a.factration)) AS NOV,\r\n\r\nSUM(decode(TO_CHAR(a.telfeedate, \u2018mm\u2019), \u201912\u2032, a.factration)) AS DEC\r\n\r\nFROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration\r\n\r\nFROM TELFEESTAND a, TELFEE b\r\n\r\nWHERE a.tel = b.telfax) a\r\n\r\nGROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, \u2018yyyy\u2019)\r\n<\/pre>\n* \u8bf4\u660e\uff1a\u56db\u8868\u8054\u67e5\u95ee\u9898<\/p>\n
\r\nselect * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where \u2026..\r\n<\/pre>\n* \u8bf4\u660e\uff1a\u5f97\u5230\u8868\u4e2d\u6700\u5c0f\u7684\u672a\u4f7f\u7528\u7684ID\u53f7<\/p>\n
\r\nSELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID \u2013 1 FROM Handle a)\r\n<\/pre>\n* \u4e00\u4e2aSQL\u8bed\u53e5\u7684\u95ee\u9898:\u884c\u5217\u8f6c\u6362<\/p>\n
\r\nselect * from v_temp\r\n\u4e0a\u9762\u7684\u89c6\u56fe\u7ed3\u679c\u5982\u4e0b:\r\nuser_name role_name\r\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014-\r\n\u7cfb\u7edf\u7ba1\u7406\u5458 \u7ba1\u7406\u5458\r\nfeng \u7ba1\u7406\u5458\r\nfeng \u4e00\u822c\u7528\u6237\r\ntest \u4e00\u822c\u7528\u6237\r\n\u60f3\u628a\u7ed3\u679c\u53d8\u6210\u8fd9\u6837:\r\nuser_name role_name\r\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\r\n\u7cfb\u7edf\u7ba1\u7406\u5458 \u7ba1\u7406\u5458\r\nfeng \u7ba1\u7406\u5458,\u4e00\u822c\u7528\u6237\r\ntest \u4e00\u822c\u7528\u6237\r\n===================\r\ncreate table a_test(name varchar(20),role2 varchar(20))\r\ninsert into a_test values(\u2018\u674e\u2019,'\u7ba1\u7406\u5458\u2019)\r\ninsert into a_test values(\u2018\u5f20\u2019,'\u7ba1\u7406\u5458\u2019)\r\ninsert into a_test values(\u2018\u5f20\u2019,'\u4e00\u822c\u7528\u6237\u2019)\r\ninsert into a_test values(\u2018\u5e38\u2019,'\u4e00\u822c\u7528\u6237\u2019)\r\n\r\ncreate function join_str(@content varchar(100))\r\nreturns varchar(2000)\r\nas\r\nbegin\r\ndeclare @str varchar(2000)\r\nset @str=\u201d\r\nselect @str=@str+\u2019,'+rtrim(role2) from a_test where [name]=@content\r\nselect @str=right(@str,len(@str)-1)\r\nreturn @str\r\nend\r\ngo\r\n\r\n\u2013\u8c03\u7528\uff1a\r\nselect [name],dbo.join_str([name]) role2 from a_test group by [name]\r\n\r\n\u2013select distinct name,dbo.uf_test(name) from a_test\r\n<\/pre>\n* \u5feb\u901f\u6bd4\u8f83\u7ed3\u6784\u76f8\u540c\u7684\u4e24\u8868
\n\u7ed3\u6784\u76f8\u540c\u7684\u4e24\u8868\uff0c\u4e00\u8868\u6709\u8bb0\u5f553\u4e07\u6761\u5de6\u53f3\uff0c\u4e00\u8868\u6709\u8bb0\u5f552\u4e07\u6761\u5de6\u53f3\uff0c\u6211\u600e\u6837\u5feb\u901f\u67e5\u627e\u4e24\u8868\u7684\u4e0d\u540c\u8bb0\u5f55\uff1f
\n\u7ed9\u4f60\u4e00\u4e2a\u6d4b\u8bd5\u65b9\u6cd5\uff0c\u4ecenorthwind\u4e2d\u7684orders\u8868\u53d6\u6570\u636e\u3002<\/p>\n\r\nselect * into n1 from orders\r\nselect * into n2 from orders\r\n\r\nselect * from n1\r\nselect * from n2\r\n<\/pre>\n\u2013\u6dfb\u52a0\u4e3b\u952e\uff0c\u7136\u540e\u4fee\u6539n1\u4e2d\u82e5\u5e72\u5b57\u6bb5\u7684\u82e5\u5e72\u6761<\/p>\n
\r\nalter table n1 add constraint pk_n1_id primary key (OrderID)\r\nalter table n2 add constraint pk_n2_id primary key (OrderID)\r\n\r\nselect OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1\r\n<\/pre>\n\u5e94\u8be5\u53ef\u4ee5\uff0c\u800c\u4e14\u5c06\u4e0d\u540c\u7684\u8bb0\u5f55\u7684ID\u663e\u793a\u51fa\u6765\u3002
\n\u4e0b\u9762\u7684\u9002\u7528\u4e8e\u53cc\u65b9\u8bb0\u5f55\u4e00\u6837\u7684\u60c5\u51b5\uff0c<\/p>\n\r\nselect * from n1 where orderid in (select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)\r\n<\/pre>\n\u81f3\u4e8e\u53cc\u65b9\u4e92\u4e0d\u5b58\u5728\u7684\u8bb0\u5f55\u662f\u6bd4\u8f83\u597d\u5904\u7406\u7684
\n\u2013\u5220\u9664n1,n2\u4e2d\u82e5\u5e72\u6761\u8bb0\u5f55<\/p>\n\r\ndelete from n1 where orderID in (\u201910728\u2032,\u201910730\u2032)\r\ndelete from n2 where orderID in (\u201911000\u2032,\u201911001\u2032)\r\n<\/pre>\n\u2013 \u53cc\u65b9\u90fd\u6709\u8be5\u8bb0\u5f55\u5374\u4e0d\u5b8c\u5168\u76f8\u540c<\/p>\n
\r\nselect * from n1 where orderid in(select OrderID from (select * from n1 union select * from n2) a group by OrderID having count(*) > 1)\r\nunion\r\n\u2013n2\u4e2d\u5b58\u5728\u4f46\u5728n1\u4e2d\u4e0d\u5b58\u7684\u572810728,10730\r\nselect * from n1 where OrderID not in (select OrderID from n2)\r\nunion\r\n\u2013n1\u4e2d\u5b58\u5728\u4f46\u5728n2\u4e2d\u4e0d\u5b58\u7684\u572811000,11001\r\nselect * from n2 where OrderID not in (select OrderID from n1)\r\n<\/pre>\n* \u56db\u79cd\u65b9\u6cd5\u53d6\u8868\u91ccn\u5230m\u6761\u7eaa\u5f55\uff1a<\/p>\n
1.<\/p>\n
\r\nselect top m * into \u4e34\u65f6\u8868(\u6216\u8868\u53d8\u91cf) from tablename order by columnname \u2014 \u5c06top m\u7b14\u63d2\u5165\r\nset rowcount n\r\nselect * from \u8868\u53d8\u91cf order by columnname desc\r\n<\/pre>\n2.<\/p>\n
\r\nselect top n * from (select top m * from tablename order by columnname) a order by columnname desc\r\n<\/pre>\n3.\u5982\u679ctablename\u91cc\u6ca1\u6709\u5176\u4ed6identity\u5217\uff0c\u90a3\u4e48\uff1a<\/p>\n
\r\nselect identity(int) id0,* into #temp from tablename\r\n<\/pre>\n\u53d6n\u5230m\u6761\u7684\u8bed\u53e5\u4e3a\uff1a<\/p>\n
\r\nselect * from #temp where id0 >=n and id0 <= m\r\n<\/pre>\n\u5982\u679c\u4f60\u5728\u6267\u884cselect identity(int) id0,* into #temp from tablename\u8fd9\u6761\u8bed\u53e5\u7684\u65f6\u5019\u62a5\u9519,\u90a3\u662f\u56e0\u4e3a\u4f60\u7684DB\u4e2d\u95f4\u7684select into\/bulkcopy\u5c5e\u6027\u6ca1\u6709\u6253\u5f00\u8981\u5148\u6267\u884c\uff1a<\/p>\n
\r\nexec sp_dboption \u4f60\u7684DB\u540d\u5b57,\u2019select into\/bulkcopy\u2019,true\r\n<\/pre>\n4.\u5982\u679c\u8868\u91cc\u6709identity\u5c5e\u6027\uff0c\u90a3\u4e48\u7b80\u5355\uff1a<\/p>\n
\r\nselect * from tablename where identitycol between n and m\r\n<\/pre>\n* \u5982\u4f55\u5220\u9664\u4e00\u4e2a\u8868\u4e2d\u91cd\u590d\u7684\u8bb0\u5f55\uff1f<\/p>\n
\r\ncreate table a_dist(id int,name varchar(20))\r\n\r\ninsert into a_dist values(1,\u2019abc\u2019)\r\ninsert into a_dist values(1,\u2019abc\u2019)\r\ninsert into a_dist values(1,\u2019abc\u2019)\r\ninsert into a_dist values(1,\u2019abc\u2019)\r\n\r\nexec up_distinct \u2018a_dist\u2019,'id\u2019\r\n\r\nselect * from a_dist\r\n\r\ncreate procedure up_distinct(@t_name varchar(30),@f_key varchar(30))\r\n\r\n\u2013f_key\u8868\u793a\u662f\u5206\u7ec4\u5b57\u6bb5\ufe50\u5373\u4e3b\u952e\u5b57\u6bb5\r\n\r\nas\r\nbegin\r\ndeclare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer\r\nselect @sql = \u2018declare cur_rows cursor for select \u2018+@f_key+\u2019 ,count(*) from \u2018 +@t_name +\u2019 group by \u2018 +@f_key +\u2019 having count(*) > 1\u2032\r\nexec(@sql)\r\nopen cur_rows\r\nfetch cur_rows into @id,@max\r\nwhile @@fetch_status=0\r\nbegin\r\nselect @max = @max -1\r\nset rowcount @max\r\nselect @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key\r\nif @type=56\r\nselect @sql = \u2018delete from \u2018+@t_name+\u2019 where \u2018 + @f_key+\u2019 = \u2018+ @id\r\nif @type=167\r\nselect @sql = \u2018delete from \u2018+@t_name+\u2019 where \u2018 + @f_key+\u2019 = \u2018+\u201d\u201d+ @id +\u201d\u201d\r\nexec(@sql)\r\nfetch cur_rows into @id,@max\r\nend\r\nclose cur_rows\r\ndeallocate cur_rows\r\nset rowcount 0\r\nend\r\n\r\nselect * from systypes\r\nselect * from syscolumns where id = object_id(\u2018a_dist\u2019)\r\n<\/pre>\n* \u67e5\u8be2\u6570\u636e\u7684\u6700\u5927\u6392\u5e8f\u95ee\u9898\uff08\u53ea\u80fd\u7528\u4e00\u6761\u8bed\u53e5\u5199\uff09<\/p>\n
\r\nCREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))\r\n\r\ninsert into hard values (\u2018A\u2019,\u20191\u2032,3)\r\ninsert into hard values (\u2018A\u2019,\u20192\u2032,4)\r\ninsert into hard values (\u2018A\u2019,\u20194\u2032,2)\r\ninsert into hard values (\u2018A\u2019,\u20196\u2032,9)\r\ninsert into hard values (\u2018B\u2019,\u20191\u2032,4)\r\ninsert into hard values (\u2018B\u2019,\u20192\u2032,5)\r\ninsert into hard values (\u2018B\u2019,\u20193\u2032,6)\r\ninsert into hard values (\u2018C\u2019,\u20193\u2032,4)\r\ninsert into hard values (\u2018C\u2019,\u20196\u2032,7)\r\ninsert into hard values (\u2018C\u2019,\u20192\u2032,3)\r\n<\/pre>\n\u8981\u6c42\u67e5\u8be2\u51fa\u6765\u7684\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n
qu co je
\n\u2014\u2014\u2014\u2013 \u2014\u2014\u2014\u2013 \u2014\u2013
\nA 6 9
\nA 2 4
\nB 3 6
\nB 2 5
\nC 6 7
\nC 3 4<\/p>\n\u5c31\u662f\u8981\u6309qu\u5206\u7ec4\uff0c\u6bcf\u7ec4\u4e2d\u53d6je\u6700\u5927\u7684\u524d2\u4f4d\uff01\uff01
\n\u800c\u4e14\u53ea\u80fd\u7528\u4e00\u53e5sql\u8bed\u53e5\uff01\uff01\uff01<\/p>\n\r\nselect * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)\r\n<\/pre>\n* \u6c42\u5220\u9664\u91cd\u590d\u8bb0\u5f55\u7684sql\u8bed\u53e5\uff1f
\n\u600e\u6837\u628a\u5177\u6709\u76f8\u540c\u5b57\u6bb5\u7684\u7eaa\u5f55\u5220\u9664\uff0c\u53ea\u7559\u4e0b\u4e00\u6761\u3002
\n\u4f8b\u5982\uff0c\u8868test\u91cc\u6709id,name\u5b57\u6bb5
\n\u5982\u679c\u6709name\u76f8\u540c\u7684\u8bb0\u5f55 \u53ea\u7559\u4e0b\u4e00\u6761\uff0c\u5176\u4f59\u7684\u5220\u9664\u3002
\nname\u7684\u5185\u5bb9\u4e0d\u5b9a\uff0c\u76f8\u540c\u7684\u8bb0\u5f55\u6570\u4e0d\u5b9a\u3002
\n\u6709\u6ca1\u6709\u8fd9\u6837\u7684sql\u8bed\u53e5\uff1f<\/p>\nA:\u4e00\u4e2a\u5b8c\u6574\u7684\u89e3\u51b3\u65b9\u6848\uff1a<\/p>\n
\u5c06\u91cd\u590d\u7684\u8bb0\u5f55\u8bb0\u5165temp1\u8868:<\/p>\n
\r\nselect [\u6807\u5fd7\u5b57\u6bb5id],count(*) into temp1 from [\u8868\u540d]\r\ngroup by [\u6807\u5fd7\u5b57\u6bb5id]\r\nhaving count(*)>1\r\n<\/pre>\n2\u3001\u5c06\u4e0d\u91cd\u590d\u7684\u8bb0\u5f55\u8bb0\u5165temp1\u8868:<\/p>\n
\r\ninsert temp1 select [\u6807\u5fd7\u5b57\u6bb5id],count(*) from [\u8868\u540d] group by [\u6807\u5fd7\u5b57\u6bb5id] having count(*)=1\r\n<\/pre>\n3\u3001\u4f5c\u4e00\u4e2a\u5305\u542b\u6240\u6709\u4e0d\u91cd\u590d\u8bb0\u5f55\u7684\u8868\uff1a<\/p>\n
\r\nselect * into temp2 from [\u8868\u540d] where \u6807\u5fd7\u5b57\u6bb5id in(select \u6807\u5fd7\u5b57\u6bb5id from temp1)\r\n<\/pre>\n4\u3001\u5220\u9664\u91cd\u590d\u8868:<\/p>\n
\r\ndelete [\u8868\u540d]\r\n<\/pre>\n5\u3001\u6062\u590d\u8868\uff1a<\/p>\n
\r\ninsert [\u8868\u540d] select * from temp2\r\n<\/pre>\n6\u3001\u5220\u9664\u4e34\u65f6\u8868:<\/p>\n
\r\ndrop table temp1\r\ndrop table temp2\r\n<\/pre>\nB:<\/p>\n
\r\ncreate table a_dist(id int,name varchar(20))\r\n\r\ninsert into a_dist values(1,\u2019abc\u2019)\r\ninsert into a_dist values(1,\u2019abc\u2019)\r\ninsert into a_dist values(1,\u2019abc\u2019)\r\ninsert into a_dist values(1,\u2019abc\u2019)\r\n\r\nexec up_distinct \u2018a_dist\u2019,'id\u2019\r\n\r\nselect * from a_dist\r\n\r\ncreate procedure up_distinct(@t_name varchar(30),@f_key varchar(30))\r\n\u2013f_key\u8868\u793a\u662f\u5206\u7ec4\u5b57\u6bb5\ufe50\u5373\u4e3b\u952e\u5b57\u6bb5\r\nas\r\nbegin\r\ndeclare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer\r\nselect @sql = \u2018declare cur_rows cursor for select \u2018+@f_key+\u2019 ,count(*) from \u2018 +@t_name +\u2019 group by \u2018 +@f_key +\u2019 having count(*) > 1\u2032\r\nexec(@sql)\r\nopen cur_rows\r\nfetch cur_rows into @id,@max\r\nwhile @@fetch_status=0\r\nbegin\r\nselect @max = @max -1\r\nset rowcount @max\r\nselect @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key\r\nif @type=56\r\nselect @sql = \u2018delete from \u2018+@t_name+\u2019 where \u2018 + @f_key+\u2019 = \u2018+ @id\r\nif @type=167\r\nselect @sql = \u2018delete from \u2018+@t_name+\u2019 where \u2018 + @f_key+\u2019 = \u2018+\u201d\u201d+ @id +\u201d\u201d\r\nexec(@sql)\r\nfetch cur_rows into @id,@max\r\nend\r\nclose cur_rows\r\ndeallocate cur_rows\r\nset rowcount 0\r\nend\r\n\r\nselect * from systypes\r\nselect * from syscolumns where id = object_id(\u2018a_dist\u2019)\r\n<\/pre>\n* \u884c\u5217\u8f6c\u6362\u2013\u666e\u901a<\/p>\n
\u5047\u8bbe\u6709\u5f20\u5b66\u751f\u6210\u7ee9\u8868(CJ)\u5982\u4e0b
\nName Subject Result
\n\u5f20\u4e09 \u8bed\u6587 80
\n\u5f20\u4e09 \u6570\u5b66 90
\n\u5f20\u4e09 \u7269\u7406 85
\n\u674e\u56db \u8bed\u6587 85
\n\u674e\u56db \u6570\u5b66 92
\n\u674e\u56db \u7269\u7406 82<\/p>\n\u60f3\u53d8\u6210
\n\u59d3\u540d \u8bed\u6587 \u6570\u5b66 \u7269\u7406
\n\u5f20\u4e09 80 90 85
\n\u674e\u56db 85 92 82<\/p>\n\r\ndeclare @sql varchar(4000)\r\nset @sql = \u2018select Name\u2019\r\nselect @sql = @sql + \u2018,sum(case Subject when \u201d\u2019+Subject+\u201d\u2019 then Result end) ['+Subject+']\u2018\r\nfrom (select distinct Subject from CJ) as a\r\nselect @sql = @sql+\u2019 from test group by name\u2019\r\nexec(@sql)\r\n<\/pre>\n\u884c\u5217\u8f6c\u6362\u2013\u5408\u5e76<\/p>\n
\u6709\u8868A,
\nid pid
\n1 1
\n1 2
\n1 3
\n2 1
\n2 2
\n3 1
\n\u5982\u4f55\u5316\u6210\u8868B:
\nid pid
\n1 1,2,3
\n2 1,2
\n3 1<\/p>\n\u521b\u5efa\u4e00\u4e2a\u5408\u5e76\u7684\u51fd\u6570<\/p>\n
\r\ncreate function fmerg(@id int)\r\nreturns varchar(8000)\r\nas\r\nbegin\r\ndeclare @str varchar(8000)\r\nset @str=\u201d\r\nselect @str=@str+\u2019,'+cast(pid as varchar) from \u8868A where id=@id\r\nset @str=right(@str,len(@str)-1)\r\nreturn(@str)\r\nEnd\r\ngo\r\n\r\n\u2013\u8c03\u7528\u81ea\u5b9a\u4e49\u51fd\u6570\u5f97\u5230\u7ed3\u679c\r\nselect distinct id,dbo.fmerg(id) from \u8868A\r\n<\/pre>\n* \u5982\u4f55\u53d6\u5f97\u4e00\u4e2a\u6570\u636e\u8868\u7684\u6240\u6709\u5217\u540d<\/p>\n
\u65b9\u6cd5\u5982\u4e0b\uff1a\u5148\u4eceSYSTEMOBJECT\u7cfb\u7edf\u8868\u4e2d\u53d6\u5f97\u6570\u636e\u8868\u7684SYSTEMID,\u7136\u540e\u518dSYSCOLUMN\u8868\u4e2d\u53d6\u5f97\u8be5\u6570\u636e\u8868\u7684\u6240\u6709\u5217\u540d\u3002
\nSQL\u8bed\u53e5\u5982\u4e0b\uff1a<\/p>\n\r\ndeclare @objid int,@objname char(40)\r\nset @objname = \u2018tablename\u2019\r\nselect @objid = id from sysobjects where id = object_id(@objname)\r\nselect \u2018Column_name\u2019 = name from syscolumns where id = @objid order by colid\r\n\r\n\u6216\r\n\r\nSELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =\u2019users\u2019\r\n<\/pre>\n* \u901a\u8fc7SQL\u8bed\u53e5\u6765\u66f4\u6539\u7528\u6237\u7684\u5bc6\u7801<\/p>\n
\u4fee\u6539\u522b\u4eba\u7684,\u9700\u8981sysadmin role<\/p>\n
\r\nEXEC sp_password NULL, \u2018newpassword\u2019, \u2018User\u2019\r\n<\/pre>\n\u5982\u679c\u5e10\u53f7\u4e3aSA\u6267\u884cEXEC sp_password NULL, \u2018newpassword\u2019, sa<\/p>\n
* \u600e\u4e48\u5224\u65ad\u51fa\u4e00\u4e2a\u8868\u7684\u54ea\u4e9b\u5b57\u6bb5\u4e0d\u5141\u8bb8\u4e3a\u7a7a\uff1f<\/p>\n
\r\nselect COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=\u2019NO\u2019 and TABLE_NAME=tablename\r\n<\/pre>\n* \u5982\u4f55\u5728\u6570\u636e\u5e93\u91cc\u627e\u5230\u542b\u6709\u76f8\u540c\u5b57\u6bb5\u7684\u8868\uff1f
\na. \u67e5\u5df2\u77e5\u5217\u540d\u7684\u60c5\u51b5<\/p>\n\r\nSELECT b.name as TableName,a.name as columnname\r\nFrom syscolumns a INNER JOIN sysobjects b\r\nON a.id=b.id\r\nAND b.type=\u2019U\u2019\r\nAND a.name=\u2019\u4f60\u7684\u5b57\u6bb5\u540d\u5b57\u2019\r\n<\/pre>\n* \u672a\u77e5\u5217\u540d\u67e5\u6240\u6709\u5728\u4e0d\u540c\u8868\u51fa\u73b0\u8fc7\u7684\u5217\u540d<\/p>\n
\r\nSelect o.name As tablename,s1.name As columnname\r\nFrom syscolumns s1, sysobjects o\r\nWhere s1.id = o.id\r\nAnd o.type = \u2018U\u2019\r\nAnd Exists (\r\nSelect 1 From syscolumns s2\r\nWhere s1.name = s2.name\r\nAnd s1.id <> s2.id\r\n)\r\n<\/pre>\n* \u67e5\u8be2\u7b2cxxx\u884c\u6570\u636e<\/p>\n
\u5047\u8bbeid\u662f\u4e3b\u952e\uff1a<\/p>\n
\r\nselect * from (select top xxx * from yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)\r\n<\/pre>\n\u5982\u679c\u4f7f\u7528\u6e38\u6807\u4e5f\u662f\u53ef\u4ee5\u7684<\/p>\n
\r\nfetch absolute [number] from [cursor_name]\r\n<\/pre>\n\u884c\u6570\u4e3a\u7edd\u5bf9\u884c\u6570<\/p>\n
* SQL Server\u65e5\u671f\u8ba1\u7b97<\/p>\n
\r\na. \u4e00\u4e2a\u6708\u7684\u7b2c\u4e00\u5929\r\nSELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)\r\nb. \u672c\u5468\u7684\u661f\u671f\u4e00\r\nSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)\r\nc. \u4e00\u5e74\u7684\u7b2c\u4e00\u5929\r\nSELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)\r\nd. \u5b63\u5ea6\u7684\u7b2c\u4e00\u5929\r\nSELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)\r\ne. \u4e0a\u4e2a\u6708\u7684\u6700\u540e\u4e00\u5929\r\nSELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))\r\nf. \u53bb\u5e74\u7684\u6700\u540e\u4e00\u5929\r\nSELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))\r\ng. \u672c\u6708\u7684\u6700\u540e\u4e00\u5929\r\nSELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))\r\nh. \u672c\u6708\u7684\u7b2c\u4e00\u4e2a\u661f\u671f\u4e00\r\nselect DATEADD(wk, DATEDIFF(wk,0,\r\ndateadd(dd,6-datepart(day,getdate()),getdate())\r\n), 0)\r\ni. \u672c\u5e74\u7684\u6700\u540e\u4e00\u5929\r\nSELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))\u3002\r\n<\/pre>\n* \u83b7\u53d6\u8868\u7ed3\u6784[\u628a ‘sysobjects’ \u66ff\u6362 \u6210 ‘tablename’ \u5373\u53ef]<\/p>\n
\r\nSELECT CASE IsNull(I.name, \u201d)\r\nWhen \u201d Then \u201d\r\nElse \u2018*\u2019\r\nEnd as IsPK,\r\nObject_Name(A.id) as t_name,\r\nA.name as c_name,\r\nIsNull(SubString(M.text, 1, 254), \u201d) as pbc_init,\r\nT.name as F_DataType,\r\nCASE IsNull(TYPEPROPERTY(T.name, \u2018Scale\u2019), \u201d)\r\nWHEN \u201d Then Cast(A.prec as varchar)\r\nELSE Cast(A.prec as varchar) + \u2018,\u2019 + Cast(A.scale as varchar)\r\nEND as F_Scale,\r\nA.isnullable as F_isNullAble\r\nFROM Syscolumns as A\r\nJOIN Systypes as T\r\nON (A.xType = T.xUserType AND A.Id = Object_id(\u2018sysobjects\u2019) )\r\nLEFT JOIN ( SysIndexes as I\r\nJOIN Syscolumns as A1\r\nON ( I.id = A1.id and A1.id = object_id(\u2018sysobjects\u2019) and (I.status & 0\u00d7800) = 0\u00d7800 AND A1.colid <= I.keycnt) )\r\nON ( A.id = I.id AND A.name = index_col(\u2018sysobjects\u2019, I.indid, A1.colid) )\r\nLEFT JOIN SysComments as M\r\nON ( M.id = A.cdefault and ObjectProperty(A.cdefault, \u2018IsConstraint\u2019) = 1 )\r\nORDER BY A.Colid ASC\r\n<\/pre>\n* \u63d0\u53d6\u6570\u636e\u5e93\u5185\u6240\u6709\u8868\u7684\u5b57\u6bb5\u8be6\u7ec6\u8bf4\u660e\u7684SQL\u8bed\u53e5<\/p>\n
\r\nSELECT\r\n(case when a.colorder=1 then d.name else \u201d end) N\u2019\u8868\u540d\u2019,\r\na.colorder N\u2019\u5b57\u6bb5\u5e8f\u53f7\u2019,\r\na.name N\u2019\u5b57\u6bb5\u540d\u2019,\r\n(case when COLUMNPROPERTY( a.id,a.name,\u2019IsIdentity\u2019)=1 then \u2018\u221a\u2019else \u201d\r\nend) N\u2019\u6807\u8bc6\u2019,\r\n(case when (SELECT count(*)\r\nFROM sysobjects\r\nWHERE (name in\r\n(SELECT name\r\nFROM sysindexes\r\nWHERE (id = a.id) AND (indid in\r\n(SELECT indid\r\nFROM sysindexkeys\r\nWHERE (id = a.id) AND (colid in\r\n(SELECT colid\r\nFROM syscolumns\r\nWHERE (id = a.id) AND (name = a.name))))))) AND\r\n(xtype = \u2018PK\u2019))>0 then \u2018\u221a\u2019 else \u201d end) N\u2019\u4e3b\u952e\u2019,\r\nb.name N\u2019\u7c7b\u578b\u2019,\r\na.length N\u2019\u5360\u7528\u5b57\u8282\u6570\u2019,\r\nCOLUMNPROPERTY(a.id,a.name,\u2019PRECISION\u2019) as N\u2019\u957f\u5ea6\u2019,\r\nisnull(COLUMNPROPERTY(a.id,a.name,\u2019Scale\u2019),0) as N\u2019\u5c0f\u6570\u4f4d\u6570\u2019,\r\n(case when a.isnullable=1 then \u2018\u221a\u2019else \u201d end) N\u2019\u5141\u8bb8\u7a7a\u2019,\r\nisnull(e.text,\u201d) N\u2019\u9ed8\u8ba4\u503c\u2019,\r\nisnull(g.[value],\u201d) AS N\u2019\u5b57\u6bb5\u8bf4\u660e\u2019\r\nFROM syscolumns a\r\nleft join systypes b\r\non a.xtype=b.xusertype\r\ninner join sysobjects d\r\non a.id=d.id and d.xtype=\u2019U\u2019 and d.name<>\u2019dtproperties\u2019\r\nleft join syscomments e\r\non a.cdefault=e.id\r\nleft join sysproperties g\r\non a.id=g.id AND a.colid = g.smallid\r\norder by object_name(a.id),a.colorder\r\n<\/pre>\n* \u5feb\u901f\u83b7\u53d6\u8868test\u7684\u8bb0\u5f55\u603b\u6570[\u5bf9\u5927\u5bb9\u91cf\u8868\u975e\u5e38\u6709\u6548]<\/p>\n
\u5feb\u901f\u83b7\u53d6\u8868test\u7684\u8bb0\u5f55\u603b\u6570:<\/p>\n
\r\nselect rows from sysindexes where id = object_id(\u2018test\u2019) and indid in (0,1)\r\n\r\nupdate 2 set KHXH=(ID+1)\\2 2\u884c\u9012\u589e\u7f16\u53f7\r\nupdate [23] set id1 = \u2018No.\u2019+right(\u201900000000\u2032+id,6) where id not like \u2018No%\u2019 \/\/\u9012\u589e\r\nupdate [23] set id1= \u2018No.\u2019+right(\u201900000000\u2032+replace(id1,\u2019No.\u2019,\u201d),6) \/\/\u8865\u4f4d\u9012\u589e\r\ndelete from [1] where (id%2)=1\r\n<\/pre>\n\u5947\u6570<\/p>\n
* \u66ff\u6362\u8868\u540d\u5b57\u6bb5<\/p>\n
\r\nupdate [1] set domurl = replace(domurl,\u2019Upload\/Imgswf\/\u2019,'Upload\/Photo\/\u2019) where domurl like \u2018%Upload\/Imgswf\/%\u2019\r\n<\/pre>\n* \u622a\u4f4d<\/p>\n
\r\nSELECT LEFT(\u8868\u540d, 5)\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"* \u8bf4\u660e\uff1a\u590d\u5236\u8868(\u53ea\u590d\u5236\u7ed3\u6784,\u6e90\u8868\u540d\uff1aa \u65b0\u8868\u540d\uff1ab) select * into b from a wher […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-281","post","type-post","status-publish","format-standard","hentry","category-code_related"],"_links":{"self":[{"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/posts\/281","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/comments?post=281"}],"version-history":[{"count":1,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/posts\/281\/revisions"}],"predecessor-version":[{"id":5322,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/posts\/281\/revisions\/5322"}],"wp:attachment":[{"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/media?parent=281"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/categories?post=281"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kyle.ai\/blog\/wp-json\/wp\/v2\/tags?post=281"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}