A 表:singleloadno styleno
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENOB表
material numtotal styleno
13412501 20 STYLENO
13412501 34 STYLENO要求结果:
material numtotal styleno singleloadno(数量)
13412501 54 STYLENO 9
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENO
LID STYLENOB表
material numtotal styleno
13412501 20 STYLENO
13412501 34 STYLENO要求结果:
material numtotal styleno singleloadno(数量)
13412501 54 STYLENO 9
解决方案 »
- 如何区别中、英文的标点符号
- Identity属性 列操作问题
- 都来帮我看看这个SQL怎么写
- 怎样知道syscomments表中那些字段代表是视图,存储过程,用户自定义函数?
- MSSQL订阅问题,订阅数据库没有数据
- 继续纠结动态游标问题
- 有外键约束的字段能为空吗?谢谢!
- 急。对数据库的一个表做了误操作。update的时候没加where.致使这个表里几十条记录全部成了一条。以前的不记得了。
- select * into newtable from oldtable 复制表在ASA 7.0中该怎么写??
- 不小心删除数据库中某一个表的许多记录,该数据库没有备份,能不能恢复?
- 求一条sql语句
- 求一段SQL
GO
SET NOCOUNT ON
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GO
IF OBJECT_ID(N'B',N'U')IS NOT NULL DROP TABLE B
GOCREATE TABLE A--创建测试数据表
(
singleloadno nvarchar(15),
styleno nvarchar(15)
)
INSERT INTO A--插入测试数据
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO'
go
CREATE TABLE B--创建测试数据表
(
material nvarchar(15),
numtotal int,
styleno nvarchar(15)
)
INSERT INTO B--插入测试数据
SELECT '13412501', 20, 'STYLENO' UNION ALL
SELECT '13412501', 64, 'STYLENO' UNION ALL
SELECT '13412501', 20, 'STYLENO1' UNION ALL
SELECT '13412501', 34, 'STYLENO1' UNION ALL
SELECT '134125011', 30, 'STYLENO' UNION ALL
SELECT '134125011', 34, 'STYLENO' UNION ALL
SELECT '134125011', 200, 'STYLENO1' UNION ALL
SELECT '134125011', 34, 'STYLENO1'
gowith cte as
(
select material ,sum(numtotal) as numtotal,styleno from B
group by material,styleno
)
select material,numtotal,A.styleno,COUNT(singleloadno) AS singleloadno
from A inner join cte on cte.styleno=A.styleno GROUP BY material,numtotal,A.styleno
ORDER BY materialgo
IF OBJECT_ID(N'A',N'U')IS NOT NULL DROP TABLE A
GO
IF OBJECT_ID(N'B',N'U')IS NOT NULL DROP TABLE B
GO/*
material numtotal styleno singleloadno
--------------- ----------- --------------- ------------
13412501 54 STYLENO1 8
13412501 84 STYLENO 9
134125011 64 STYLENO 9
134125011 234 STYLENO1 8
*/
select b.material,sum(b.numtotal) numtotal,b.styleno,count(*) as singleloadno
from b left join a on b.styleno = a.styleno
group by b.material,b.styleno
CREATE TABLE A--创建测试数据表
(
singleloadno nvarchar(15),
styleno nvarchar(15)
)
INSERT INTO A--插入测试数据
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO1' UNION ALL
SELECT 'LID', 'STYLENO'
go
CREATE TABLE B--创建测试数据表
(
material nvarchar(15),
numtotal int,
styleno nvarchar(15)
)
INSERT INTO B--插入测试数据
SELECT '13412501', 20, 'STYLENO' UNION ALL
SELECT '13412501', 64, 'STYLENO' UNION ALL
SELECT '13412501', 20, 'STYLENO1' UNION ALL
SELECT '13412501', 34, 'STYLENO1' UNION ALL
SELECT '134125011', 30, 'STYLENO' UNION ALL
SELECT '134125011', 34, 'STYLENO' UNION ALL
SELECT '134125011', 200, 'STYLENO1' UNION ALL
SELECT '134125011', 34, 'STYLENO1'
go实现SQL如下:select material,sum(numtotal) numtotal, styleno
, singleloadno = (select count(*) from a where styleno=b.styleno)
from b
group by material, styleno执行结果:material numtotal styleno singleloadno
-----------------------------------------------
13412501 84 STYLENO 9
134125011 64 STYLENO 9
13412501 54 STYLENO1 8
134125011 234 STYLENO1 8
from b left join a on b.styleno = a.styleno
group by b.material,b.styleno
CREATE TABLE A--创建测试数据表
(
singleloadno nvarchar(15),
styleno nvarchar(15)
)
INSERT INTO A--插入测试数据
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO' UNION ALL
SELECT 'LID', 'STYLENO'
go
CREATE TABLE B--创建测试数据表
(
material nvarchar(15),
numtotal int,
styleno nvarchar(15)
)
INSERT INTO B--插入测试数据
SELECT '13412501', 20, 'STYLENO' UNION ALL
SELECT '13412501', 34, 'STYLENO'
goselect b.material,sum(numtotal)numtotal,b.styleno,
(select count(*) from a where b.styleno = a.styleno)singleloadno
from b
group by b.material,b.styleno/*
material numtotal styleno singleloadno
--------------- ----------- --------------- ------------
13412501 54 STYLENO 9(所影响的行数为 1 行)
*/