SELECT * , group_concat(' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , if(`quantity-purchased`> 1, '****' , ' ') ),group_concat(' ' , `order_id` , ' ') FROM ( select * from `sale_orders` t WHERE `number`=$id AND (1<(select count(*) from `sale_orders` where `recipient-name`=t.`recipient-name`) OR `quantity-purchased`>1) order by `sku`) T WHERE `shipments` =0 AND `number` =$id GROUP BY `recipient-name` , `ship-address-1` ORDER BY length(group_concat( ' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , ' ' )) DESC,`sku`以上我目前的代码,但是查到的结果不是我想要的
我是想在以下查询的基础上
select * from `sale_orders` t WHERE `number`=$id AND (1<(select count(*) from `sale_orders` where `recipient-name`=t.`recipient-name`) OR `quantity-purchased`>1) order by `sku`执行以下查询
SELECT * , group_concat(' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , if(`quantity-purchased`> 1, '****' , ' ') ),group_concat(' ' , `order_id` , ' ') FROM ( select * from `sale_orders` order by `sku`) T WHERE `shipments` =0 AND `number` =$id GROUP BY `recipient-name` , `ship-address-1` ORDER BY length(group_concat( ' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , ' ' )) DESC,`sku`
我是想在以下查询的基础上
select * from `sale_orders` t WHERE `number`=$id AND (1<(select count(*) from `sale_orders` where `recipient-name`=t.`recipient-name`) OR `quantity-purchased`>1) order by `sku`执行以下查询
SELECT * , group_concat(' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , if(`quantity-purchased`> 1, '****' , ' ') ),group_concat(' ' , `order_id` , ' ') FROM ( select * from `sale_orders` order by `sku`) T WHERE `shipments` =0 AND `number` =$id GROUP BY `recipient-name` , `ship-address-1` ORDER BY length(group_concat( ' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , ' ' )) DESC,`sku`
提示什么错误?
select * from `sale_orders` t WHERE `number`=$id AND (1<(select count(*) from `sale_orders` where `recipient-name`=t.`recipient-name`) OR `quantity-purchased`>1) order by `sku``quantity-purchased`>1
???这句能用吗?怎么理解?
number int(11)
order_id varchar(40)
order_item_id bigint(40)
shipments int(11)
payments-date varchar(50)
buyer-email tinytext
buyer-name tinytext
buyer-phone-number varchar(50)
sku varchar(50)
jj_sku varchar(50)
product-name text
quantity-purchased int(11)
recipient-name tinytext
ship-address-1 text
ship-address-2 text
ship-address-3 text
ship-state tinytext
ship-postal-code varchar(50)
ship-country varchar(50)
account varchar(30)
ship-city varchar(30)
cancel_order int(2)
delivery_areas
number字段是类似批号,就是这次输入的都是一样的number需求是这样的:
先查询出number=$id(某一批)recipient-name(购买人姓名)出现过大于1次或者购买数量大于1的订单,就是以下查询
select * from `sale_orders` t WHERE `number`=$id AND (1<(select count(*) from `sale_orders` where `recipient-name`=t.`recipient-name`) OR `quantity-purchased`>1) order by `sku`
然后对上面那个查询结果进行汇总处理:
SELECT * , group_concat(' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , if(`quantity-purchased`> 1, '****' , ' ') ),group_concat(' ' , `order_id` , ' ') FROM ( select * from `sale_orders` order by `sku`) T WHERE `shipments` =0 AND `number` =$id GROUP BY `recipient-name` , `ship-address-1` ORDER BY length(group_concat( ' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , ' ' )) DESC,`sku`以前是两个查询分开的,现在要求在第一个查询基础上进行汇总。SELECT * , group_concat(' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , if(`quantity-purchased`> 1, '****' , ' ') ),group_concat(' ' , `order_id` , ' ') FROM ( select * from `sale_orders` t WHERE `number`=$id AND (1<(select count(*) from `sale_orders` where `recipient-name`=t.`recipient-name`) OR `quantity-purchased`>1) order by `sku`) T WHERE `shipments` =0 AND `number` =$id GROUP BY `recipient-name` , `ship-address-1` ORDER BY length(group_concat( ' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , ' ' )) DESC,`sku`这段是我自己改的,语句本身没有错,只是查询的结果不是我想要的,结果是所有订单都进行汇总了,我是要对第一个查询结果(筛选过后的结果)的基础上进行汇总
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
number
order_id
order_item_id
shipments
payments-date
buyer-email
buyer-name
buyer-phone-number
sku
jj_sku
roduct-name
quantity-purchased
recipient-name
ship-address-1
ship-address-2
ship-address-3
ship-state
ship-postal-code
ship-country
account
ship-city
cancel_order
delivery_areas 原始数据
number recipient-name sku quantity-purchased delivery_areas
1 王刚 BK00001 2 HK
1 王刚 BA00001 1 HK
1 李红 BK00001 2 JP
1 刘三 YE00001 1 HK
1 陈二 CD00001 2 JP
1 陈三 JH00001 1 HK
1 李四 DD00001 2 JP我想要的就是对Number=1,相同recipient-name出现超过1次或者quantity-purchased>1的数据进行汇总结果:
number recipient-name group_concat(' ' , concat_ws( ' X ', `sku` , `quantity-purchased` ) , if(`quantity-purchased`> 1, '****' , ' ') ) 1 王刚 BK00001 X 2**** , BA00001 X 1
1 李红 BK00001 X 2****
1 陈二 CD00001 X 2****
1 李四 DD00001 X 2****