常用SQL语句(1)
Createtable#tmp(uuidint,cNamenvarchar(50)) |
insertinto#tmpvalues(1,'苹果') |
insertinto#tmpvalues(2,'葡萄') |
insertinto#tmpvalues(1,'苹果111') |
insertinto#tmpvalues(2,'葡萄111') |
insertinto#tmpvalues(1,'苹果222') |
insertinto#tmpvalues(3,'橘子') |
--查询 |
Select*from#tmp |
--将结果拼接在一起 |
Selectx.uuid,left(cName,len(cName)-1)ascName |
from ( |
Selectuuid |
,(SelectcName+','from#tmpwhereuuid=t1.uuidforxmlpath(''))ascName |
from#tmpast1 |
groupbyuuid |
)x |
ps: 在 SQL SERVER 中只有用 for xml 的写法才能实现该功能。 |
--添加记录 |
insertinto[dbo].[T_BU_STOCK] |
([STORAGE_YEAR],[STORAGE_MONTH],[STORAGE_DAY],[STORAGE_DATE],[VEHICLE_BELONG],[CAR_SERIES_CODE]) |
Values('2015','7','22','2015-07-22','1','CR-V') |
--添加记录 |
insertinto#T_BU_STOCK |
([STORAGE_YEAR],[STORAGE_MONTH],[STORAGE_DAY],[STORAGE_DATE],[VEHICLE_BELONG],[CAR_SERIES_CODE]) |
Select 2015,7,22,'2015-07-22','1','CR-V' |
--查询表的结果 |
insertinto#T_BU_STOCK |
([STORAGE_YEAR],[STORAGE_MONTH],[STORAGE_DAY],[STORAGE_DATE],[VEHICLE_BELONG],[CAR_SERIES_CODE]) |
Select 2015,7,22,'2015-07-22','1','CR-V' |
from#T_BU_STOCK |
--执行存储过程方式(存储过程返回的值必须要和添加表的列值结果一致) |
insertinto#T_BU_STOCK |
EXECPdd_Stock |
--备份表 |
Select*intoT_BU_DLR_2015_08_20fromT_BU_DLR |
--创建临时表 |
Select*into#T_BU_DLR_2015_08_20fromT_BU_DLR |
分享标题:常用SQL语句(1)
链接地址:http://scyanting.com/article/igjcgp.html