B表
b字段
5
6
15
13A表
ID gid(物品id) B表b字段
1 7 5
2 8 6
3 9 15
4 10 13A表只有id,gid两字段,查询时怎么把B表的b字段也插入进去
b字段
5
6
15
13A表
ID gid(物品id) B表b字段
1 7 5
2 8 6
3 9 15
4 10 13A表只有id,gid两字段,查询时怎么把B表的b字段也插入进去
解决方案 »
- 递增更新重复值,结果却没有递增,这是为什么?
- 在mssql2005中,修改某一字段的值,浏览显示这个字段的页面时,又恢复到修改前的值,是缓存造成的?
- 分类表的设计问题,请兄弟们不啬指教
- 存储过程的一个问题
- 累积数值
- 假设在机器Tserver上有db01,db02,..等库,这些库具有相同结构。如何将其中某个库的某些记录复制到另外一个库中?
- HELP: 怎样将SQL SERVER表里的数据导入到Access表中?
- 2005的库日志被删除,怎么恢复?
- 请问怎样查找一段日期中有日期间断,并显示出来
- SQL server 数据文件与表的关系(菜鸟问题)
- sql 查询替换带逗号的字段
- sql 2008 r2 安装报错
ID gid(物品id) B表b字段
1 7 13
2 8 15
3 9 6
4 10 5
有办法实现
有办法实现语句怎么写
select * from a join b on a.gid = b.gid
是这样的
select front_num from sr_jxc_inventory_log
where add_time>='2017-11-01 00:00:00'
查询出的数据是
5
6
15
13我想把这个数据作为 期初数据(cqjc) 插入到下面的语句中
select L.goods_id,L.warehouse_id, G.model, G.factory,G.barcode, W.name,
sum(L.actual_number) jc,
sum(case when L.actual_number> 0 then L.actual_number else 0 end) as sr,
sum(case when L.actual_number <0 then L.actual_number else 0 end ) as zc,
(select front_num from `sr_jxc_inventory_log`
where warehouse_id='1' and
add_time>'2017-11-02 00:00:00'
order by add_time
limit 1)as cqjc,
(SUM(L.actual_number)+(select front_num from `sr_jxc_inventory_log`
where warehouse_id='1' and
add_time>'2017-11-02 00:00:00'
order by add_time
limit 1)
) as qmjc
FROM sr_jxc_inventory_log as L
LEFT JOIN sr_goods as G ON G.goods_id = L.goods_id
LEFT JOIN sr_jxc_ware as W ON W.wareid = L.warehouse_id
where L.warehouse_id='1' and L.add_time>'2017-11-02 00:00:00'
group by L.goods_id;这里面的cqjc 查询后都显示为5
select L.goods_id,L.warehouse_id, G.model, G.factory,G.barcode, W.name,
sum(L.actual_number) jc,
sum(case when L.actual_number> 0 then L.actual_number else 0 end) as sr,
sum(case when L.actual_number <0 then L.actual_number else 0 end ) as zc,
B.front_num as cqjc,
(SUM(L.actual_number)+(select front_num from `sr_jxc_inventory_log`
where warehouse_id='1' and
add_time>'2017-11-02 00:00:00'
order by add_time
limit 1)
) as qmjc
FROM sr_jxc_inventory_log as L
LEFT JOIN sr_goods as G ON G.goods_id = L.goods_id
LEFT JOIN sr_jxc_ware as W ON W.wareid = L.warehouse_id
LEFT JOIN
(select front_num,goods_id
from `sr_jxc_inventory_log` as A
where warehouse_id='1'
and add_time>'2017-11-02 00:00:00'
and not exists (select 1 from `sr_jxc_inventory_log`
where goods_id=A.goods_id and add_time<A.add_time and add_time>'2017-11-02 00:00:00' )) as B ON L.goods_id=B.goods_id
where L.warehouse_id='1' and L.add_time>'2017-11-02 00:00:00'
group by L.goods_id;