STOREID , GOODSID , SQT , IQT , OQT, CQT , NGOODSID , DD1, DD2
1001 3001 100 30 , 40 , 80 , 8001 ,DDA , DDB
1001 3001 100 30 , 40 , 80 , 8003 ,DDB , DDC
1001 3001 100 30 , 40 , 80 , 8005 ,DDK , DDB1001 3002 100 30 , 40 , 80 , 8005 ,DDK , DDB1001 3008 100 30 , 40 , 80 , 8007 ,DDB , DDC
1001 3008 100 30 , 40 , 80 , 8008 ,DDK , DDB把前面的字段 STOREID, GOODSID , SQT, IQT, OQT, CQT 均相同时只保留一行,同时SQT,IQT,OQT,CQT 设置为0
要求结果如下:
STOREID, GOODSID , SQT, IQT, OQT, CQT, NGID ,DD1, DD2
1001 3001 100 30, 40, 80 , 8001 ,DDA , DDB
1001 3001 0 0 , 0 , 0, 8003 ,DDB , DDC
1001 3001 0 0 , 0 , 0 8005 ,DDK , DDB1001 3002 100 30 , 40 , 80 , 8005 ,DDK , DDB1001 3008 100 30 , 40 , 80 , 8007 ,DDB , DDC
1001 3008 0 0 , 0 , 0 , 8008 ,DDK , DDB
1001 3001 100 30 , 40 , 80 , 8001 ,DDA , DDB
1001 3001 100 30 , 40 , 80 , 8003 ,DDB , DDC
1001 3001 100 30 , 40 , 80 , 8005 ,DDK , DDB1001 3002 100 30 , 40 , 80 , 8005 ,DDK , DDB1001 3008 100 30 , 40 , 80 , 8007 ,DDB , DDC
1001 3008 100 30 , 40 , 80 , 8008 ,DDK , DDB把前面的字段 STOREID, GOODSID , SQT, IQT, OQT, CQT 均相同时只保留一行,同时SQT,IQT,OQT,CQT 设置为0
要求结果如下:
STOREID, GOODSID , SQT, IQT, OQT, CQT, NGID ,DD1, DD2
1001 3001 100 30, 40, 80 , 8001 ,DDA , DDB
1001 3001 0 0 , 0 , 0, 8003 ,DDB , DDC
1001 3001 0 0 , 0 , 0 8005 ,DDK , DDB1001 3002 100 30 , 40 , 80 , 8005 ,DDK , DDB1001 3008 100 30 , 40 , 80 , 8007 ,DDB , DDC
1001 3008 0 0 , 0 , 0 , 8008 ,DDK , DDB
解决方案 »
- 求一高效查询[sql2000中]
- select convert(datetime ,'2006-10-16')+convert(datetime,'10:31:16.258')的结果????
- 怎么查看数据库被那些远程用户连接了?
- 除了使用查询分析器之外,还有什么工具能够编辑存储过程???
- 两个表的关联问题!在线等等,答者有分!急!
- 把一个表得记录数存放到变量里面,但是选择得表也是由变量决定得,怎么写sql
- 求助,sql分组统计
- group by 的时候,有没有把字段合并起来.
- 如何设计实现这样的数据库???
- 请问:如何用SQL语句判断一个字段是否主键或外键,如果是的话就选出,请指教!!
- 求教将数据库查询出的结果再插入本表其他列的方法。
- 高分求 在线提问 如何查询数据库中数据并向客户端发送信息 在线等待中 来者有分
WHERE NOT EXISTS
(SELECT 1 FROM TB WHERE T.STOREID=STOREID AND T.GOODSID=GOODSID AND T.SQT=SQT AND
IQT=T.IQT AND OQT=T.OQT AND T.CQT=CQT AND NGOODSID <T.NGOODSID )UNION ALLSELECT STOREID , GOODSID , 0, 0, 0, 0, NGOODSID , DD1, DD2
FROM TB T
WHERE EXISTS
(SELECT 1 FROM TB WHERE T.STOREID=STOREID AND T.GOODSID=GOODSID AND T.SQT=SQT AND
IQT=T.IQT AND OQT=T.OQT AND T.CQT=CQT AND NGOODSID <T.NGOODSID )
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([STOREID] int,[GOODSID] int,[SQT] int,[IQT] int,[OQT] int,[CQT] int,[NGOODSID] int,[DD1] varchar(3),[DD2] varchar(3))
insert [TB]
select 1001,3001,100,30,40,80,8001,'DDA','DDB' union all
select 1001,3001,100,30,40,80,8003,'DDB','DDC' union all
select 1001,3001,100,30,40,80,8005,'DDK','DDB' union all
select 1001,3002,100,30,40,80,8005,'DDK','DDB' union all
select 1001,3008,100,30,40,80,8007,'DDB','DDC' union all
select 1001,3008,100,30,40,80,8008,'DDK','DDB'
update TB set [SQT]=0,[IQT]=0,[OQT]=0,[CQT]=0 from TB t where exists
(select 1 from TB where [STOREID]+[GOODSID]+[SQT]+[IQT]+[OQT]+[CQT]=t.[STOREID]+t.[GOODSID]+t.[SQT]+t.[IQT]+t.[OQT]+t.[CQT] and t.[NGOODSID]>[NGOODSID])select * from TB/*
STOREID GOODSID SQT IQT OQT CQT NGOODSID DD1 DD2
----------- ----------- ----------- ----------- ----------- ----------- ----------- ---- ----
1001 3001 100 30 40 80 8001 DDA DDB
1001 3001 0 0 0 0 8003 DDB DDC
1001 3001 0 0 0 0 8005 DDK DDB
1001 3002 100 30 40 80 8005 DDK DDB
1001 3008 100 30 40 80 8007 DDB DDC
1001 3008 0 0 0 0 8008 DDK DDB(6 行受影响)
*/
drop table TB
(SELECT 1 FROM TB WHERE T.STOREID=STOREID AND T.GOODSID=GOODSID AND T.SQT=SQT AND
IQT=T.IQT AND OQT=T.OQT AND T.CQT=CQT AND NGOODSID <T.NGOODSID )请问这里为什么用 AND NGOODSID <T.NGOODSID
NGOODSID <T.NGOODSID 这样做保证 你不为0 的那行为第一行。。