案例:一个简单的表Inventory有100以上的信息,其中包含:
Item Color Quantity
--------------------------------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red Null
Chair Red 210
........
以下请使用sql或Oracle语句完成:1,按Item分类,列出各类商品的数量.
2,按Item分类,仅列出各类商品中红色多于蓝色的商品名称及差额数量.
3,按Item分类,将数据按下列方式进行统计显示:
Item Red Blue
Item Color Quantity
--------------------------------------------------
Table Blue 124
Table Red 223
Chair Blue 101
Chair Red Null
Chair Red 210
........
以下请使用sql或Oracle语句完成:1,按Item分类,列出各类商品的数量.
2,按Item分类,仅列出各类商品中红色多于蓝色的商品名称及差额数量.
3,按Item分类,将数据按下列方式进行统计显示:
Item Red Blue
解决方案 »
- 改变查询出来的值类型
- 不能连接数据库,原因是“不能产生sspi上下文!”---解决了散分 100
- 问个基本概念:触发器的inserted表里,是不是只有当前进程执行的insert记录,有没有可能该表中还有其它进程insert时的记录呢???
- 服务器下多个login 映射某数据库的一个user
- 请高手解决replistor同步后,MSSQL数据库出现了“置疑”
- 存储过程中的循环
- 将截断字符串或二进制数据 如果我就不管就强制插入让它截断怎么操作
- 统计的问题??快呀??
- sql server 长时间update后 update出现time out--急找邹建急找邹建急找邹建大哥!!!!!!!!在线急等!!!不睡觉不吃饭等!
- 新手
- 求一简单SQL语句
- 请教一个算法问题
Item,
sum(case when Color='Red' then isnull(Quantity,0) else 0 end) as Red,
sum(case when Color='Blue' then isnull(Quantity,0) else 0 end) as Blue
from
Inventory
group by Item
(select item,count(*) 数量 from inventory where Color = 'Red' group by item) m
(select item,count(*) 数量 from inventory where Color = 'Blue' group by item) n
where m.item = n.item and m.数量 > n.数量
3,按Item分类,将数据按下列方式进行统计显示:
Item Red Blueselect item ,
sum(case color when 'red' then quantity else 0 end) Red,
sum(case color when 'blue' then quantity else 0 end) Blue
from tb
group by item
*,Red-Blue as 红色多于蓝色的商品名称及差额数量
from
(select
Item,
sum(case when Color='Red' then isnull(Quantity,0) else 0 end) as Red,
sum(case when Color='Blue' then isnull(Quantity,0) else 0 end) as Blue
from
Inventory
group by Item)T
where
Red>Blue
--1,按Item分类,列出各类商品的数量.
--2,按Item分类,仅列出各类商品中红色多于蓝色的商品名称及差额数量.
--3,按Item分类,将数据按下列方式进行统计显示:
--Item Red Blue
declare @tb table (item varchar(10),color varchar(10),quantity int)
insert into @tb select 'Table','Blue',124
insert into @tb select 'Table','Red',223
insert into @tb select 'Chair','Blue',101
insert into @tb select 'Chair','Red',null
insert into @tb select 'Chair','Red',210
--1
select item,count(item) as 'count' from @tb group by item
--2
select tpa.item,quantitya-quantityb as '差' from (
select item,sum(quantity) as quantitya from @tb where color='red' group by item) tpa left join (
select item,sum(quantity) as quantityb from @tb where color='blue' group by item)tpb
on tpa.item=tpb.item
where quantitya>quantityb
--3
select item,
sum(case when color='red' then quantity else 0 end) as 'red',
sum(case when color='blue' then quantity else 0 end) as 'blue'
from @tb
group by item
create table inventory (item varchar(10),color varchar(10),quantity int)
insert into inventory select 'Table','Blue',124
insert into inventory select 'Table','Red',223
insert into inventory select 'Chair','Blue',101
insert into inventory select 'Chair','Red',0
insert into inventory select 'Chair','Red',210
insert into inventory select 'Chair','Blue',301--1、
select item,sum(Quantity)Quantity from inventory group by item--2、
select * from
(select Item,sum(case when Color='Red' then isnull(Quantity,0) else 0 end)-
sum(case when Color='Blue' then isnull(Quantity,0) else 0 end) [差]
from Inventory group by Item)a
where [差]>0--3没有样式。
select
Item,
sum(Quantity) as 各类商品的数量,from
Inventory
group by Item
insert into tb values('Table', 'Blue', 124 )
insert into tb values('Table', 'Red' , 223)
insert into tb values('Chair', 'Blue', 101)
insert into tb values('Chair', 'Red' , Null )
insert into tb values('Chair', 'Red' , 210 )
go--1
select item,count(*) 数量 from tb group by item
/*
item 数量
---------- -----------
Chair 3
Table 2(所影响的行数为 2 行)
*/--2
select m.item , m.数量-n.数量 差额数量 from
(select item,count(*) 数量 from tb where Color = 'Red' group by item) m,
(select item,count(*) 数量 from tb where Color = 'Blue' group by item) n
where m.item = n.item and m.数量 > n.数量
/*
item 差额数量
---------- -----------
Chair 1(所影响的行数为 1 行)
*/--3
select item ,
sum(case color when 'red' then quantity else 0 end) Red,
sum(case color when 'blue' then quantity else 0 end) Blue
from tb
group by item
/*
item Red Blue
---------- ----------- -----------
Chair 210 101
Table 223 124(所影响的行数为 2 行)
*/drop table tb