67,513
社区成员
发帖
与我相关
我的任务
分享List集合怎么做到这样
id 姓名
1 小明
2 小张
3 小李
id 年龄
1 20
3 22
id 姓名 年龄
1 小明 20
2 小张 null
3 小李 22
表里面3个字段都有，怎么用List集合做到这样，注意是List集合
1 小明
2 小张
3 小李
id 年龄
1 20
3 22
id 姓名 年龄
1 小明 20
2 小张 null
3 小李 22
表里面3个字段都有，怎么用List集合做到这样，注意是List集合
...全文
请发表友善的回复…
发表回复
安澜 2017-07-21
- 打赏
- 举报
那么请教一下sql语句怎么让
变成那样[/quote]
改很简单，但是兄弟，我不想一个字一个字敲啊
[/quote]select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice as '单价',c.MinimumValue as '最低控货价值',
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as '今日进仓',
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as '今日出仓'
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue
select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as '昨日剩余库存'
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model
这是我的两条原sql语句[/quote]
少打了点东西
select x.Consignee,x.ItemName,x.zrkc AS '昨日剩余库存',y.StoreName,y.EqpPrice as '单价',y.MinimumValue as '最低控货价值',y.jrjc as '今日进仓',y.jrcc as '今日出仓'
(select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as zrkc
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model)x LEFT JOIN
(select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice ,c.MinimumValue ,
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as jrjc,
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as jrcc
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue)
ON x.Consignee = y.Consignee[/quote]
谢谢你，我根据你的sql写出来了
Bad_Milo_ 2017-07-21
- 打赏
- 举报
那么请教一下sql语句怎么让
变成那样[/quote]
改很简单，但是兄弟，我不想一个字一个字敲啊[/quote]
select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice as '单价',c.MinimumValue as '最低控货价值',
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as '今日进仓',
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as '今日出仓'
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue
select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as '昨日剩余库存'
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model
这是我的两条原sql语句[/quote]
少打了点东西
select x.Consignee,x.ItemName,x.zrkc AS '昨日剩余库存',y.StoreName,y.EqpPrice as '单价',y.MinimumValue as '最低控货价值',y.jrjc as '今日进仓',y.jrcc as '今日出仓'
(select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as zrkc
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model)x LEFT JOIN
(select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice ,c.MinimumValue ,
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as jrjc,
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as jrcc
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue)
ON x.Consignee = y.Consignee
变成那样[/quote]
改很简单，但是兄弟，我不想一个字一个字敲啊[/quote]
select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice as '单价',c.MinimumValue as '最低控货价值',
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as '今日进仓',
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as '今日出仓'
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue
select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as '昨日剩余库存'
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model
这是我的两条原sql语句[/quote]
少打了点东西
select x.Consignee,x.ItemName,x.zrkc AS '昨日剩余库存',y.StoreName,y.EqpPrice as '单价',y.MinimumValue as '最低控货价值',y.jrjc as '今日进仓',y.jrcc as '今日出仓'
(select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as zrkc
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model)x LEFT JOIN
(select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice ,c.MinimumValue ,
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as jrjc,
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as jrcc
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue)
ON x.Consignee = y.ConsigneeBad_Milo_ 2017-07-21
- 打赏
- 举报
那么请教一下sql语句怎么让
变成那样[/quote]
改很简单，但是兄弟，我不想一个字一个字敲啊[/quote]
select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice as '单价',c.MinimumValue as '最低控货价值',
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as '今日进仓',
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as '今日出仓'
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue
select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as '昨日剩余库存'
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model
这是我的两条原sql语句[/quote]
试下吧
select x.Consignee,x.ItemName,x.zrkc AS '昨日剩余库存',y.StoreName,y.EqpPrice as '单价',y.MinimumValue as '最低控货价值',y.jrjc as '今日进仓',y.jrcc as '今日出仓'
(select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as zrkc
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model)x LEFT JOIN
(select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice ,c.MinimumValue ,
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as jrjc,
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as jrcc
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue)
ON
变成那样[/quote]
改很简单，但是兄弟，我不想一个字一个字敲啊[/quote]
select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice as '单价',c.MinimumValue as '最低控货价值',
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as '今日进仓',
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as '今日出仓'
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue
select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as '昨日剩余库存'
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model
这是我的两条原sql语句[/quote]
试下吧
select x.Consignee,x.ItemName,x.zrkc AS '昨日剩余库存',y.StoreName,y.EqpPrice as '单价',y.MinimumValue as '最低控货价值',y.jrjc as '今日进仓',y.jrcc as '今日出仓'
(select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as zrkc
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model)x LEFT JOIN
(select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice ,c.MinimumValue ,
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as jrjc,
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as jrcc
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue)
ONBad_Milo_ 2017-07-21
- 打赏
- 举报
说说大体思路吧，select你想要的字段 把 图里下面的查询结果作为表x left join 图里上面的查询结果作为表y on x.Consignee= y.Consignee
安澜 2017-07-21
- 打赏
- 举报
那么请教一下sql语句怎么让
变成那样[/quote]
改很简单，但是兄弟，我不想一个字一个字敲啊[/quote]
select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice as '单价',c.MinimumValue as '最低控货价值',
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as '今日进仓',
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as '今日出仓'
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue
select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as '昨日剩余库存'
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model
这是我的两条原sql语句
变成那样[/quote]
改很简单，但是兄弟，我不想一个字一个字敲啊[/quote]
select DISTINCT a.Consignee,a.ItemName,a.StoreName,e.EqpPrice as '单价',c.MinimumValue as '最低控货价值',
sum(CASE WHEN Wtype = '进仓' THEN a.Quantity end) as '今日进仓',
sum(CASE WHEN Wtype = '出仓' THEN a.Quantity end) as '今日出仓'
FROM ACCESS_STORAGE a inner join EQP_MESSAGE e on a.ItemName = e.EqpName
left join CONTRACT c on a.Consignee=c.Consignee
where a.Consignee='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())=0
GROUP BY a.ItemName,a.Model,a.StoreName,a.Consignee,e.EqpPrice,c.MinimumValue
select DISTINCT Consignee,ItemName,
SUM(CASE WHEN Wtype = '进仓' THEN Quantity ELSE - Quantity END) as '昨日剩余库存'
FROM ACCESS_STORAGE where Consignee ='鼎识科技' and DATEDIFF(day,EnterTime,GETDATE())>=1
GROUP BY ItemName,Consignee,Model
这是我的两条原sql语句安澜 2017-07-21
- 打赏
- 举报
怎么让他们变成这样
企业 名称 昨日剩余库存 仓库 单价 最低控货价值 今日进仓数量 今日出仓数量
鼎识科技 钢材 3000 2号仓库 3540 5000000 7000 6000
鼎识科技 石油 5000
鼎识科技 铁 7000 2号仓库 2450 5000000 15000 NULL
Bad_Milo_ 2017-07-21
- 打赏
- 举报
那么请教一下sql语句怎么让
变成那样[/quote]
改很简单，但是兄弟，我不想一个字一个字敲啊
变成那样[/quote]
改很简单，但是兄弟，我不想一个字一个字敲啊
weSilo 2017-07-21
- 打赏
- 举报
如果你这个关联很常用的话，可以在数据库里建一个视图
安澜 2017-07-21
- 打赏
- 举报
那么请教一下sql语句怎么让
变成那样
110成成 2017-07-21
- 打赏
- 举报
数据库两张表关联下，查出来放入List就行了。
Bad_Milo_ 2017-07-21
- 打赏
- 举报
sql语句查询出来用LIST接收不就好了吗
安澜 2017-07-21
- 打赏
- 举报
或者将两个list在jsp页面用<c:forEach> 遍历的时候展示成下面那样也行
安澜 2017-07-21
- 打赏
- 举报
补充，这是让两个List合并为一个List变成下面那样
