统计查询两列相同的sql语句

展开阅读全文

sql语句问题,单列查询两列

06-24

下面的是测试数据,查询出来的数据效果如下面图片所示;rnrn这里表中字段和效果图片字段对应关系rn主要的:rn ids----------->物料编号rn name---------->品名规格rn datetimes----->日期rn action-------->类型rn orderNo------->单号rn counts-------->数量rnrnrn表中counts字段值为正数的是“本期入仓”的信息,值为负数的是“本期出仓”的信息,rn同一天的“本期入仓”“本期出仓”没有关联,显示不分顺序。rn图片中除了本期入仓和本期出仓外的表格中空格(这里的空格是没有数据)的地方,其实是有数据的,因为是相同的rn数据,所以清空了。还原的话,类似如下(没数据的地方我用“——”代替)rnca0000101,壁虎胶,白色,pcs,2011-3-22,——,货品入仓,FI11030004,2555,车间领料,MC11030001,255,2400rnca0000101,壁虎胶,白色,pcs,2011-3-22,——,货品入仓,FI11030005,100 ,车间领料,MC11030001,255,2400rnca0000101,壁虎胶,白色,pcs,2011-3-23,2400,————,—————,——,货品出仓,FC11030001,100,2290rnca0000101,壁虎胶,白色,pcs,2011-3-22,2400,————,—————,——,货品出仓,FC11030002,10 ,2290rnrn请问:rnrn如何查询出如图效果?rnrnrn[img=http://hi.csdn.net/attachment/201106/24/221964_1308909943QV5m.jpg][/img]rnrn测试数据:rn[code=SQL]rnif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[productTotalView]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)rndrop table [dbo].[productTotalView]rnGOrnrnCREATE TABLE [dbo].[productTotalView] (rn [id] [int] IDENTITY (1, 1) NOT NULL ,rn [ids] [int] NULL ,rn [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,rn [type] [nvarchar] (5) COLLATE Chinese_PRC_CI_AS NULL ,rn [datetimes] [datetime] NULL ,rn [action] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,rn [orderNo] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,rn [counts] [decimal](18, 4) NULL rn) ON [PRIMARY]rnGOrnrninsert into productTotalView values (1,'刮板','A','2011-04-05','in','FI20110405001',20.0000)rninsert into productTotalView values (223,'六角螺母','A','2011-04-05 00:00:00','in','FI20110405001',20.0000)rninsert into productTotalView values (15,'水晶灯罩','A','2011-05-01 00:00:00','out','FI20110601023',-20.0000)rninsert into productTotalView values (24,'一字挂板','A','2011-05-08 00:00:00','in','FI20110601023',20.0000)rninsert into productTotalView values (2,'刮板2','A','2011-04-05 00:00:00','in','FI20110601023',20.0000)rninsert into productTotalView values (24,'一字挂板','A','2011-05-08 00:00:00','counts','FI20110601023',20.0000)rninsert into productTotalView values (24,'一字挂板','A','2011-05-08 00:00:00','out','FI20110601023',-20.0000)rninsert into productTotalView values (24,'一字挂板','A','2011-05-10 00:00:00','in','FI20110601023',20.0000)rninsert into productTotalView values (2,'刮板2','A','2011-04-07 00:00:00','in','FI20110601023',20.0000)rninsert into productTotalView values (15,'水晶灯罩','A','2011-05-01 00:00:00','in','FI20110601023',20.0000)rninsert into productTotalView values (15,'水晶灯罩','A','2011-05-01 00:00:00','counts','FI20110601023',-20.0000)rninsert into productTotalView values (15,'水晶灯罩','A','2011-05-12 00:00:00','counts','FI20110601023',20.0000)rninsert into productTotalView values (15,'水晶灯罩','A','2011-05-12 00:00:00','in','FI20110601023',20.0000)rninsert into productTotalView values (223,'六角螺母','A','2011-04-05 00:00:00','in','FI20110601023',20.0000)rninsert into productTotalView values (223,'六角螺母','A','2011-04-05 00:00:00','out','FI20110601023',-20.0000)rninsert into productTotalView values (223,'六角螺母','A','2011-04-05 00:00:00','out','FI20110601023',-13.0000)rninsert into productTotalView values (117,'四角螺母','A','2011-06-23 00:00:00','in','FI20110601023',20.0000)rninsert into productTotalView values (117,'四角螺母','A','2011-06-23 00:00:00','in','FI20110601023',20.0000)rninsert into productTotalView values (1,'刮板','A','2011-04-05 00:00:00','out','FI20110405002',-22.0000)rninsert into productTotalView values (1,'刮板','A','2011-04-06 00:00:00','out','FI20110406001',-15.0000)rninsert into productTotalView values (117,'四角螺母','A','2011-06-23 00:00:00','in','FI20110601023',20.0000)rninsert into productTotalView values (223,'六角螺母','A','2011-04-05 00:00:00','counts','FI20110601023',-20.0000)rninsert into productTotalView values (223,'六角螺母','A','2011-04-12 00:00:00','in','FI20110601023',20.0000)rninsert into productTotalView values (223,'六角螺母','A','2011-04-13 00:00:00','out','FI20110601023',-20.0000)rninsert into productTotalView values (2,'刮板2','A','2011-04-07 00:00:00','out','FI20110601023',-20.0000)rninsert into productTotalView values (2,'刮板2','A','2011-04-07 00:00:00','counts','FI20110601023',20.0000)rn[/code]rnrn 论坛

没有更多推荐了,返回首页