表结构如下:ID 项目 页码 数量
1 AAA 8 1000
2 BBB 12 2000
2 CCC 12 2000
2 DDD 12 2000
3 1 4 500
3 2 4 500
3 3 4 500
4 ABC 16 3000现在想要:
ID不同保留原有记录
ID相同,项目取第一和最后一个,中间用“-”隔开
页码保留,数量累计
相同的ID页码和数量都相同
实现的结果:ID 项目 页码 数量
1 AAA 8 1000
2 BBB-DDD 12 6000
3 1-3 4 1500
4 ABC 16 3000
SQLSQL语句
1 AAA 8 1000
2 BBB 12 2000
2 CCC 12 2000
2 DDD 12 2000
3 1 4 500
3 2 4 500
3 3 4 500
4 ABC 16 3000现在想要:
ID不同保留原有记录
ID相同,项目取第一和最后一个,中间用“-”隔开
页码保留,数量累计
相同的ID页码和数量都相同
实现的结果:ID 项目 页码 数量
1 AAA 8 1000
2 BBB-DDD 12 6000
3 1-3 4 1500
4 ABC 16 3000
SQLSQL语句
select row_number() over (partition by id order by id)as id1,* from table_1
)
,tb2 as(
select id1,tb.id,项目,页码,a.数量 from tb
left join (select table_1.id,sum(数量)as 数量 from table_1 group by id)as a
on a.id = tb.id where id1 =1
)
,tb3 as (
select * from tb where id1 in (select max(id1) as id1 from tb where id1 <> 1 group by id)
)
select tb2.id,tb2.项目+ case when tb3.项目 is null then '' else '-' +tb3.项目 end as 项目 ,tb2.页码,
tb2.数量 from tb2
left join tb3 on tb2.id = tb3.id
'row_number' 不是可以识别的 函数名。
(
ID INT,
XM NVARCHAR(50),
YM INT,
SL INT
)INSERT INTO T_TEST
SELECT 1,'AAA',8,1000
UNION
SELECT 2,'BBB',12,2000
UNION
SELECT 2,'CCC',12,2000
UNION
SELECT 2,'DDD',12,2000
UNION
SELECT 3,'1',4,500
UNION
SELECT 3,'2',4,500
UNION
SELECT 3,'3',4,500
UNION
SELECT 4,'ABC',16,3000
SELECT * FROM T_TEST--取最小XM 放入临时表
SELECT ID,MIN(XM) AS XM,MIN(YM) AS YM,SUM(SL) AS SL INTO #T_TESTMIN FROM T_TEST GROUP BY ID
--取最大XM 放入临时表
SELECT ID,MAX(XM) AS XM,MAX(YM) AS YM,SUM(SL) AS SL INTO #T_TESTMAX FROM T_TEST GROUP BY ID
--查询语句
SELECT A.ID,CASE A.XM WHEN B.XM THEN A.XM ELSE A.XM+'-'+B.XM END XM,A.SL
FROM #T_TESTMIN A
LEFT JOIN #T_TESTMAX B ON A.ID=B.ID
请看一下是否对你有帮助!
我写了个SQL语句,不知道哪个效率高些:
CREATE TABLE [dbo].[tb] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FID] [int] NULL ,
[项目] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[页码] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[数量] [float] NULL
) ON [PRIMARY]INSERT INTO tb (FID,项目,页码,数量)
SELECT 1,'AAA',8,1000
UNION
SELECT 2,'BBB',12,2000
UNION
SELECT 2,'CCC',12,2000
UNION
SELECT 2,'DDD',12,2000
UNION
SELECT 3,'1',4,500
UNION
SELECT 3,'2',4,500
UNION
SELECT 3,'3',4,500
UNION
SELECT 4,'ABC',16,3000
CREATE VIEW dbo.tb_t /*合计数*/
AS
SELECT FID, SUM(数量) AS 数量
FROM dbo.tb
GROUP BY FIDCREATE VIEW dbo.tb2 /*第一个项目*/
AS
SELECT dbo.tb.ID, dbo.tb.FID, dbo.tb.项目, dbo.tb.页码, dbo.tb_t.数量 AS 数量
FROM dbo.tb INNER JOIN
dbo.tb_t ON dbo.tb.FID = dbo.tb_t.FID
WHERE (dbo.tb.ID IN
(SELECT TOP 1 a.id
FROM tb a
WHERE tb.fid = a.fid))CREATE VIEW dbo.tb3 /*最后一个项目*/AS
SELECT ID, FID, 项目, 页码
FROM dbo.tb
WHERE (ID IN
(SELECT TOP 1 a.id
FROM tb a
WHERE tb.fid = a.fid
ORDER BY a.id DESC)) AND (FID IN
(SELECT fid
FROM (SELECT fid, COUNT(*) AS ct
FROM tb
GROUP BY fid
HAVING COUNT(*) > 1) b))CREATE VIEW dbo.tb_s /*tb2和tb3联合*/
AS
SELECT dbo.tb2.ID, dbo.tb2.FID, dbo.tb2.项目 + (CASE WHEN dbo.tb3.项目 IS NULL
THEN '' ELSE '-' + dbo.tb3.项目 END) AS 项目, dbo.tb2.页码, dbo.tb2.数量
FROM dbo.tb2 LEFT OUTER JOIN
dbo.tb3 ON dbo.tb2.FID = dbo.tb3.FID
select 1, 'AAA' , '8' , 1000 union all
select 2, 'BBB' , '12' , 2000 union all
select 2, 'CCC' , '12' , 2000 union all
select 2, 'DDD' , '12' , 2000 union all
select 3, '1' , '4' , 500 union all
select 3, '2' , '4' , 500 union all
select 3, '3' , '4' , 500 union all
select 4, 'ABC' , '16' , 3000 select id , 项目=case when count(id)>1 then min(项目)+'-'+ max(项目)
else max(项目) end ,页码=max(页码),数量=sum(数量) from t1 group by ID不知道行的通不
mssql估计把concat(min(项目),'-',max(项目)) 换成(min(项目)+'-'+max(项目))就可以了
select ID as ID,
case when count(1)>1 then concat(min(项目),'-',max(项目)) else 项目 end as 项目,
页码 as 页码,
sum(数量) as 数量
from table1 group by ID order by ID asc ;
CREATE TABLE #tb
(
ID INT,
XM NVARCHAR(10),
YM INT,
SL INT
)--插入楼主数据到临时表#tb
INSERT INTO #tb
SELECT 1,'AAA',8,1000
UNION
SELECT 2,'BBB',12,2000
UNION
SELECT 2,'CCC',12,2000
UNION
SELECT 2,'DDD',12,2000
UNION
SELECT 3,'1',4,500
UNION
SELECT 3,'2',4,500
UNION
SELECT 3,'3',4,500
UNION
SELECT 4,'ABC',16,3000
--检索不同的ID,插入临时表#1
SELECT DISTINCT(ID) AS ID INTO #1
FROM #tb
GROUP BY ID
HAVING COUNT(ID)>1--将ID不同数据按楼主要求,插入临时表#tb
INSERT INTO #tb
SELECT ID,MIN(XM)+'-'+MAX(XM) AS XM,YM,SL
FROM #tb
WHERE ID IN (SELECT ID FROM #1)
GROUP BY ID,YM,SL--删除#tb中多余的数据
DELETE #tb WHERE ID IN (SELECT ID FROM #1) AND XM NOT LIKE '%-%'--检索主表数据
SELECT * FROM #tb--删除临时表#1
DROP TABLE #1
use tgq
if OBJECT_ID('test') is not null
drop table test
go
CREATE TABLE TEST
(
ID INT,
XM NVARCHAR(50),
YM INT,
SL INT
) INSERT INTO TEST
SELECT 1,'AAA',8,1000
UNION
SELECT 2,'BBB',12,2000
UNION
SELECT 2,'CCC',12,2000
UNION
SELECT 2,'DDD',12,2000
UNION
SELECT 3,'1',4,500
UNION
SELECT 3,'2',4,500
UNION
SELECT 3,'3',4,500
UNION
SELECT 4,'ABC',16,3000
--select * from test
;with sel as(
select *,ROW_NUMBER()over(partition by id,ym,sl order by xm) as row,count(1) over(PARTITION by id,ym,sl) as [count] from test
),
sel1 as(
select a.ID,xm=a.xm+'-'+b.xm from sel a join sel b on a.ID=b.ID and a.row=1 and b.row=b.count and b.count>1
)
,sel2 as(
select Id,sl=SUM(sl),YM from sel where [count]>1 group by ID,YM,SL
)
select sel2.ID,xM,ym,sl from sel2 join sel1 on sel1.ID=sel2.ID
union
select sel.id,XM,ym,sl from sel where count=1
GROUP BY id;mysql数据库下
CREATE TABLE T_TEST
(
ID INT,
XM NVARCHAR(50),
YM INT,
SL INT
)
--插入数据
INSERT INTO T_TEST
SELECT 1,'AAA',8,1000
UNION
SELECT 2,'BBB',12,2000
UNION
SELECT 2,'CCC',12,2000
UNION
SELECT 2,'DDD',12,2000
UNION
SELECT 3,'1',4,500
UNION
SELECT 3,'2',4,500
UNION
SELECT 3,'3',4,500
UNION
SELECT 4,'ABC',16,3000
--数据查询
select id,xm=case when count(id)>1 then min(xm)+'-'+ max(xm) else max(xm) end ,ym=max(ym),sl=sum(sl)
from T_TEST group by ID
这个查询速度是最快~~~