34,576
社区成员
发帖
与我相关
我的任务
分享
select a.id,b.cust_no,b.type,b.lotno,convert(varchar(10),check_time,120)
as date from stock_check as a,sys_barcode_1d_y as b where a.packageno =
b.packageno and check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00' group by a.id,b.cust_no,b.type,b.lotno,
convert(varchar(10),check_time,120) order by b.cust_no,b.lotno asc
select t.cust_no,t.type,t.lotno,count(t.lotno) as count,t.date from (select a.id,b.cust_no,b.type,b.lotno,DATE_FORMAT(check_time,'%Y-%m-%d') as date from `stock_check` as a,`sys_barcode_1d_y` as b where a.packageno = b.packageno and check_time >= '2014-02-18 08:00:00' AND check_time <= '2014-02-18 20:00:00' order by b.cust_no,b.lotno asc)t group by t.lotno,t.cust_no
select a.id,b.cust_no,b.type,b.lotno,convert(varchar(10),check_time,120)
as date from stock_check as a,sys_barcode_1d_y as b where a.packageno =
b.packageno and check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00' order by b.cust_no,b.lotno asc
数据下载地址:http://yun.baidu.com/xcloud/csdn/pan/share/link?shareid=1645611667&uk=2974027032
select max(a.id) as id,b.cust_no,b.type,b.lotno,sum(count(b.lotno))
over(partition by lotno) count,
convert(varchar(10),check_time,120) as date from stock_check as a,
sys_barcode_1d_y as b where a.packageno = b.packageno and
check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00' group by b.cust_no,b.type,b.lotno,
convert(varchar(10),check_time,120) order by b.cust_no,b.lotno asc
结果:
8296 12653627 ABH6 4037 84 2014-02-18
8299 12653627 ABH6 4041 1746 2014-02-18
8236 12654172 ABJT 4041 1746 2014-02-18
8287 12654172 ABJT 4042 750 2014-02-18[/quote]
这样呢:
select *
from
(
select max(a.id) as id,b.cust_no,b.type,b.lotno,
sum(count(b.lotno)) over(partition by lotno) count,
ROW_NUMBER() over(partition by b.lotno order by getdate()) rownum,
convert(varchar(10),check_time,120) as date
from stock_check as a,
sys_barcode_1d_y as b where a.packageno = b.packageno and
check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00'
group by b.cust_no,b.type,b.lotno,convert(varchar(10),check_time,120)
)t
where rownum = 1
order by cust_no,lotno asc
[/quote]
貌似也不对,我认真核对了下数据:
8296 12653627 ABH6 4037 84 2014-02-18
8299 12653627 ABH6 4041 51 2014-02-18
8236 12654172 ABJT 4041 1695 2014-02-18
8287 12654172 ABJT 4042 750 2014-02-18[/quote]
你有qq不,方便的话,帮你看看
select max(a.id) as id,b.cust_no,b.type,b.lotno,sum(count(b.lotno))
over(partition by lotno) count,
convert(varchar(10),check_time,120) as date from stock_check as a,
sys_barcode_1d_y as b where a.packageno = b.packageno and
check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00' group by b.cust_no,b.type,b.lotno,
convert(varchar(10),check_time,120) order by b.cust_no,b.lotno asc
结果:
8296 12653627 ABH6 4037 84 2014-02-18
8299 12653627 ABH6 4041 1746 2014-02-18
8236 12654172 ABJT 4041 1746 2014-02-18
8287 12654172 ABJT 4042 750 2014-02-18[/quote]
这样呢:
select *
from
(
select max(a.id) as id,b.cust_no,b.type,b.lotno,
sum(count(b.lotno)) over(partition by lotno) count,
ROW_NUMBER() over(partition by b.lotno order by getdate()) rownum,
convert(varchar(10),check_time,120) as date
from stock_check as a,
sys_barcode_1d_y as b where a.packageno = b.packageno and
check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00'
group by b.cust_no,b.type,b.lotno,convert(varchar(10),check_time,120)
)t
where rownum = 1
order by cust_no,lotno asc
[/quote]
貌似也不对,我认真核对了下数据:
8296 12653627 ABH6 4037 84 2014-02-18
8299 12653627 ABH6 4041 51 2014-02-18
8236 12654172 ABJT 4041 1695 2014-02-18
8287 12654172 ABJT 4042 750 2014-02-18select *
from
(
select a.id,b.cust_no,b.type,b.lotno,
sum(count(b.lotno)) over(partition by lotno) count,
ROW_NUMBER() over(partition by b.lotno order by getdate()) rownum,
convert(varchar(10),check_time,120) as date
from stock_check as a,
sys_barcode_1d_y as b where a.packageno = b.packageno and
check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00'
group by a.id,b.cust_no,b.type,b.lotno,convert(varchar(10),check_time,120)
)t
where rownum = 1
order by cust_no,lotno asc
select max(a.id) as id,b.cust_no,b.type,b.lotno,sum(count(b.lotno))
over(partition by lotno) count,
convert(varchar(10),check_time,120) as date from stock_check as a,
sys_barcode_1d_y as b where a.packageno = b.packageno and
check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00' group by b.cust_no,b.type,b.lotno,
convert(varchar(10),check_time,120) order by b.cust_no,b.lotno asc
结果:
8296 12653627 ABH6 4037 84 2014-02-18
8299 12653627 ABH6 4041 1746 2014-02-18
8236 12654172 ABJT 4041 1746 2014-02-18
8287 12654172 ABJT 4042 750 2014-02-18[/quote]
这样呢:
select *
from
(
select max(a.id) as id,b.cust_no,b.type,b.lotno,
sum(count(b.lotno)) over(partition by lotno) count,
ROW_NUMBER() over(partition by b.lotno order by getdate()) rownum,
convert(varchar(10),check_time,120) as date
from stock_check as a,
sys_barcode_1d_y as b where a.packageno = b.packageno and
check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00'
group by b.cust_no,b.type,b.lotno,convert(varchar(10),check_time,120)
)t
where rownum = 1
order by cust_no,lotno asc
select id,cust_no,type,lotno,qty,date
from
(select a.id,b.cust_no,b.type,b.lotno,
count(b.lotno) over(partition by lotno) 'qty',
convert(varchar(10),check_time,120) 'date',
row_number() over(partition by b.lotno order by a.id desc) 'rn'
from stock_check as a,sys_barcode_1d_y as b
where a.packageno=b.packageno and check_time>='2014-02-18 08:00:00'
and check_time<='2014-02-18 20:00:00') t
where t.rn=1
select max(a.id) as id,b.cust_no,b.type,b.lotno,sum(count(b.lotno))
over(partition by lotno) count,
convert(varchar(10),check_time,120) as date from stock_check as a,
sys_barcode_1d_y as b where a.packageno = b.packageno and
check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00' group by b.cust_no,b.type,b.lotno,
convert(varchar(10),check_time,120) order by b.cust_no,b.lotno asc
结果:
8296 12653627 ABH6 4037 84 2014-02-18
8299 12653627 ABH6 4041 1746 2014-02-18
8236 12654172 ABJT 4041 1746 2014-02-18
8287 12654172 ABJT 4042 750 2014-02-18select max(a.id) as id,b.cust_no,b.type,b.lotno,count(b.lotno)) over(partition by lotno),
convert(varchar(10),check_time,120)
as date from stock_check as a,sys_barcode_1d_y as b where a.packageno =
b.packageno and check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00' group by b.cust_no,b.type,b.lotno,
convert(varchar(10),check_time,120) order by b.cust_no,b.lotno asc
select id,cust_no,type,lotno,sum(count(t.lotno)) over(partition by t.lotno) cc from
(select a.id,b.cust_no,b.type,b.lotno,convert(varchar(10),check_time,120)
as date from stock_check as a,sys_barcode_1d_y as b where a.packageno =
b.packageno and check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00')t order by t.cust_no,t.lotno asc
select a.id,b.cust_no,b.type,b.lotno,sum(count(b.lotno)) over(partition by lotno),
convert(varchar(10),check_time,120)
as date from stock_check as a,sys_barcode_1d_y as b where a.packageno =
b.packageno and check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00' group by a.id,b.cust_no,b.type,b.lotno,
convert(varchar(10),check_time,120) order by b.cust_no,b.lotno asc
部分结果:
8291 12653627 ABH6 4037 84 2014/2/18
8292 12653627 ABH6 4037 84 2014/2/18
8293 12653627 ABH6 4037 84 2014/2/18
8294 12653627 ABH6 4037 84 2014/2/18
8295 12653627 ABH6 4037 84 2014/2/18
8296 12653627 ABH6 4037 84 2014/2/18
8296 12653627 ABH6 4041 1746 2014/2/18
8297 12653627 ABH6 4041 1746 2014/2/18
8298 12653627 ABH6 4041 1746 2014/2/18
8299 12653627 ABH6 4041 1746 2014/2/18
8121 12654172 ABJT 4041 1746 2014/2/18
......
8280 12654172 ABJT 4042 750 2014/2/18
8281 12654172 ABJT 4042 750 2014/2/18
8282 12654172 ABJT 4042 750 2014/2/18
8283 12654172 ABJT 4042 750 2014/2/18
8284 12654172 ABJT 4042 750 2014/2/18
8285 12654172 ABJT 4042 750 2014/2/18
8286 12654172 ABJT 4042 750 2014/2/18
8287 12654172 ABJT 4042 750 2014/2/18
正确结果应该只有3行。[/quote]
你的意思是,语句最后只有3行?select a.id,b.cust_no,b.type,b.lotno,sum(count(b.lotno)) over(partition by lotno),
convert(varchar(10),check_time,120)
as date from stock_check as a,sys_barcode_1d_y as b where a.packageno =
b.packageno and check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00' group by a.id,b.cust_no,b.type,b.lotno,
convert(varchar(10),check_time,120) order by b.cust_no,b.lotno asc
部分结果:
8291 12653627 ABH6 4037 84 2014/2/18
8292 12653627 ABH6 4037 84 2014/2/18
8293 12653627 ABH6 4037 84 2014/2/18
8294 12653627 ABH6 4037 84 2014/2/18
8295 12653627 ABH6 4037 84 2014/2/18
8296 12653627 ABH6 4037 84 2014/2/18
8296 12653627 ABH6 4041 1746 2014/2/18
8297 12653627 ABH6 4041 1746 2014/2/18
8298 12653627 ABH6 4041 1746 2014/2/18
8299 12653627 ABH6 4041 1746 2014/2/18
8121 12654172 ABJT 4041 1746 2014/2/18
......
8280 12654172 ABJT 4042 750 2014/2/18
8281 12654172 ABJT 4042 750 2014/2/18
8282 12654172 ABJT 4042 750 2014/2/18
8283 12654172 ABJT 4042 750 2014/2/18
8284 12654172 ABJT 4042 750 2014/2/18
8285 12654172 ABJT 4042 750 2014/2/18
8286 12654172 ABJT 4042 750 2014/2/18
8287 12654172 ABJT 4042 750 2014/2/18
正确结果应该只有3行。
select a.id,b.cust_no,b.type,b.lotno,convert(varchar(10),check_time,120)
as date from stock_check as a,sys_barcode_1d_y as b where a.packageno =
b.packageno and check_time >= '2014-02-18 08:00:00' AND check_time <=
'2014-02-18 20:00:00' order by b.cust_no,b.lotno asc
针对这一sql语句的结果集只对lotno求分组,但是ID,cust_no,type,lotno,date这些信息也都列出来。