如下有一个表:ID 字段1 数量 字段2 数量
1 商品1 40 商品2 30
2 商品3 22 商品1 44
3 商品2 55 商品1 22
.. ...... .... ....... ....我想求出每个商品的数量及每个商品出现的次数。并按数量的多少进行排序
1 商品1 40 商品2 30
2 商品3 22 商品1 44
3 商品2 55 商品1 22
.. ...... .... ....... ....我想求出每个商品的数量及每个商品出现的次数。并按数量的多少进行排序
解决方案 »
- sql2005客户端连接2008的问题
- 求SQL语句
- sql server 2005 新关键词
- 数据库复制
- 急!在存储过程中调用IF else 语句 提示语法错误。
- java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]ResultSet can not re-read row data
- 求较复杂sql语句?
- 数据库SQL 关联查询
- 在线等!急!怎么我用命令启动Sql server不行。
- Excel通过DTS导入数据到一个SQL Server临时表,字段类型为DTS默认,现在想知道某列中的值是否是数字,某列是否全为日期型,怎么解决?要用
- 触发器错误!!!
- sql 语句获取表主键名
(
select id,字段1 as 商品, 数量 from tb union all
select id,字段2, 数量 from tb
) t
group by 商品
(
select id,字段1 as 商品, 数量 from tb union all
select id,字段2, 数量 from tb
) t
group by 商品
order by sum(数量) desc
(
select id,字段1 as 商品, 数量1 as 数量 from tb union all
select id,字段2, 数量2 from tb
) t
group by 商品
order by sum(数量) desc
use PracticeDB
go
if exists (select 1 from sysobjects where name ='Goods')
drop table Goods
go
create table Goods (id int , 商品1 varchar(10),含量1 int,数量1 int,
商品2 varchar(10),含量2 int,数量2 int,商品3 varchar(10),含量3 int,数量3 int)
insert into Goods
select 1, '商品1', 50, 40, '商品2', 30, 55, '商品3' ,30, 55 union all
select 2, '商品3', 44, '', '商品1', 44, 60, '', '', '' union all
select 3, '商品2', 55, 60, '商品1', 22, 80, '商品3', 44 ,60 select * from Goods;with t
as
(
select 商品1 as 商品,数量1 as 数量 from Goods where 商品1<>'' and 数量1<>0
union all
select 商品2 ,数量2 from Goods where 商品2<>'' and 数量2<>0
union all
select 商品3 ,数量3 from Goods where 商品3<>'' and 数量3<>0
)
select 商品,SUM(数量) as 数量, AVG(数量) as 平均数量
from t
group by 商品
order by 数量 desc
商品 数量 平均数量
商品1 180 60
商品2 115 57
商品3 115 57
不知道是不是这样
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[商品1] [nvarchar](10),[含量1] [int],[数量1] [int],[商品2] [nvarchar](10),[含量2] [int],[数量2] [int],[商品3] [nvarchar](10),[含量3] [int],[数量3] [int])
INSERT INTO [tb]
SELECT '1','商品1','50','40','商品2','30','55','商品3','30','55' UNION ALL
SELECT '2','商品3','44','0','商品1','44','60',NULL,NULL,NULL UNION ALL
SELECT '3','商品2','55','60','商品1','22','80','商品3','44','60'--SELECT * FROM [tb]-->SQL查询如下:
SELECT 商品1 商品, SUM(含量1) 含量, SUM(数量1) 数量, COUNT(商品1) aCOUNT
FROM (
SELECT 商品1, 含量1, 数量1
FROM tb UNION ALL
SELECT 商品2, 含量2, 数量2
FROM tb UNION ALL
SELECT 商品3, 含量3, 数量3
FROM tb
) t
WHERE 商品1 IS NOT NULL
GROUP BY 商品1
ORDER BY 数量 DESC
/*
商品 含量 数量 aCOUNT
---------- ----------- ----------- -----------
商品1 116 180 3
商品2 85 115 2
商品3 118 115 3(3 行受影响)*/
SELECT 商品1 商品, SUM(含量1) 含量, SUM(数量1) 数量, COUNT(商品1) aCOUNT
(SELECT 商品1, 含量1, 数量1
FROM tb where isnull(含量1,0)*isnull(数量1,0)>0
UNION ALL
SELECT 商品2, 含量2, 数量2
FROM tb where isnull(含量2,0)*isnull(数量2,0)>0
UNION ALL
SELECT 商品3, 含量3, 数量3
FROM tb where isnull(含量3,0)*isnull(数量3,0)>0) t
WHERE 商品1 IS NOT NULL
GROUP BY 商品1
ORDER BY 数量 DESC
就这个测试数据,说说你要的结果:--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[商品1] [nvarchar](10),[含量1] [int],[数量1] [int],[商品2] [nvarchar](10),[含量2] [int],[数量2] [int],[商品3] [nvarchar](10),[含量3] [int],[数量3] [int])
INSERT INTO [tb]
SELECT '1','商品1','50','40','商品2','30','55','商品3','30','55' UNION ALL
SELECT '2','商品3','44','0','商品1','44','60',NULL,NULL,NULL UNION ALL
SELECT '3','商品2','55','60','商品1','22','80','商品3','44','60'--SELECT * FROM [tb]
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[商品1] [nvarchar](10),[含量1] [int],[数量1] [int],[商品2] [nvarchar](10),[含量2] [int],[数量2] [int],[商品3] [nvarchar](10),[含量3] [int],[数量3] [int])
INSERT INTO [tb]
SELECT '1','商品1','50','40','商品2','30','55','商品3','30','55' UNION ALL
SELECT '2','商品3','44','0','商品1','44','60',NULL,NULL,NULL UNION ALL
SELECT '3','商品2','55','60','商品1','22','80','商品3','44','60'SELECT 商品1 商品, SUM(含量1) 含量, SUM(数量1) 数量, COUNT(商品1) aCOUNT from
(SELECT 商品1, 含量1, 数量1
FROM tb where isnull(含量1,0)*isnull(数量1,0)>0
UNION ALL
SELECT 商品2, 含量2, 数量2
FROM tb where isnull(含量2,0)*isnull(数量2,0)>0
UNION ALL
SELECT 商品3, 含量3, 数量3
FROM tb where isnull(含量3,0)*isnull(数量3,0)>0
) t
WHERE t.商品1 IS NOT NULL
GROUP BY t.商品1
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[商品1] [nvarchar](10),[含量1] [int],[数量1] [int],[商品2] [nvarchar](10),[含量2] [int],[数量2] [int],[商品3] [nvarchar](10),[含量3] [int],[数量3] [int])
INSERT INTO [tb]
SELECT '1','商品1','38',NULL,'商品3','54','30','商品2','38','30' UNION ALL
SELECT '2','商品1','55','40','商品3','33','50',NULL,NULL,NULL UNION ALL
SELECT '3','商品2','38','50','商品1','54','30','商品3','38','40'SELECT 商品1 商品, SUM(含量1) 含量, SUM(数量1) 数量, COUNT(商品1) aCOUNT from
(SELECT 商品1, 含量1, 数量1
FROM tb where isnull(含量1,0)*isnull(数量1,0)>0
UNION ALL
SELECT 商品2, 含量2, 数量2
FROM tb where isnull(含量2,0)*isnull(数量2,0)>0
UNION ALL
SELECT 商品3, 含量3, 数量3
FROM tb where isnull(含量3,0)*isnull(数量3,0)>0
) t
WHERE t.商品1 IS NOT NULL
GROUP BY t.商品1我改成新的数据,不能执行
AS 平均值
FROM (SELECT ID, 商品名1 AS 商品, 数量1 AS 数量
FROM Sheet1
UNION ALL
SELECT ID, 商品名2 AS 商品, 数量2 AS 数量
FROM Sheet1 Sheet1_2
UNION ALL
SELECT ID, 商品名3, 数量3
FROM Sheet1 Sheet1_1) t
WHERE (NOT (数量 IS NULL))
GROUP BY 商品
HAVING (商品 IS NOT NULL) AND (NOT (SUM(数量) IS NULL))
ORDER BY COUNT(1) DESC, SUM(数量) DESC