sql语句

1.id          index       xm                                                 xm1         xm2         hz
----------- ----------- -------------------------------------------------- ----------- ----------- -----------
1           3           wdy                                                5           5           10
2           3           hzs                                                5           5           12
3           3           xjb                                                4           6           15
4           4           wdy                                                5           5           11
5           4           hzs                                                5           5           114
6           4           xjb                                                5           5           20
7           5           wdy                                                4           6           12
8           5           hzs                                                5           5           18
9           5           xjb                                                4           6           19

要求为：

index       wdy         hzs         xjb
----------- ----------- ----------- -----------
3           10          12          15
4           11          114         20
5           12          18          19

sql语句：

select [index],wdy=max(case when xm='wdy' then hz else 0 end),
  hzs=max(case when xm='hzs' then hz else 0 end),
  xjb=max(case when xm='xjb' then hz else 0 end)
from table_1 group by [index]

 

2.编号 名称 厂家
1 a
2 b
3 c
4 d
5 e
6 f
test2:
编号 厂家
2 广东
5 厦门
6 深圳

将第二个表中数据复制到第一个表中指定编号的某列为null数据

update test1 set test1.厂家=test2.厂家 from test2,test1
where test1.编号=test2.编号

 

3.sql server 查询列名、列数据类型、列长度 sql语句

--统计数据库里每个表的详细情况
   EXEC sp_MSforeachtable @command1="sp_spaceused '?'"

   --获得每个表的记录数和容量:
   EXEC sp_MSforeachtable @command1="print '?'",
        @command2="sp_spaceused '?'",
        @command3= "SELECT count(*) FROM ? "

--列出数据库里所有的表名
select name from sysobjects where type='U'

--列出表里的所有的列
select name from syscolumns where id=object_id('TableName')

 

4.cinvcode  iprice  ddate
010001    10      2008-01-01 
020002    20      2009-01-05
010001    10      1008-01-01
010003    30      2009-03-31
010003    30      2009-03-31
相同数据只删除一行

DELETE A FROM A T WHERE EXISTS(SELECT 1 FROM A WHERE cinvcode=T.cinvcode AND DDATE>T.DDATE)

 

5.统计某一销售销售业绩

select   销售编码,  sum(销售数量) as 销售合计数量 from   test1 group by   销售编码