如何在mysql中不使用group by条件获取记录 [英] how to get records without using group by condition in mysql
问题描述
我有下表sructures
cust_inf（cust_id，cust_name，地址，联系人）
订单（order_no，cust_id，product_name，order_qty，order_amount，order_date）
发票（inv_key，order_no，inv_date，cust_id，order_date，productname，inv_qty，inv_amount，Lr_no ，
despatch_thr）
样本数据是
cust_info
---------
i have the follwing table sructures
cust_inf(cust_id,cust_name,address,contactperson)
orders(order_no,cust_id,product_name,order_qty,order_amount,order_date)
invoices(inv_key,order_no,inv_date,cust_id,order_date,productname,inv_qty,inv_amount,Lr_no,
despatch_thr)
sample data is
cust_info
---------
<pre lang="SQL">
cust_id|cust_name|adress |contactperson
---------------------------------------
1 | xxx | aaa |vvv
2 | yyy | bbb |nnn
---------------------------------------
订单
-------
orders
-------
lang="SQL"></pre>
+----------+---------+---------------------------+-----------+--------------+-----------
| order_no | cust_id | product_name | order_qty | order_amount |order_date
+----------+---------+---------------------------+-----------+--------------+-----------<pre
| o1 | 2 | Provel 200gms Powder | 12 | 456 | 2013-11-06
| o1 | 2 | Calcy - D 10X2X10 | 15 | 810 | 2013-11-06
| o4 | 2 | Obact-200mg 10 X10 Tab | 8 | 960 | 2013-11-06
| o4 | 2 | Obact-OZ 10 X10 Tab | 7 | 1050 | 2013-11-06
| o4 | 2 | Omcid - D 10X15 | 12 | 1320 | 2013-11-06
| o2 | 1 | Pragmox -250mg 20 X10 Cap | 10 | 2330 | 2013-11-07
| o2 | 1 | Provel 200gms Powder | 15 | 465 | 2013-11-07
| o2 | 1 | Obact-200mg 10 X10 Tab | 12 | 1320 | 2013-11-07
| o2 | 1 | Obact-400mg 10 X10 Tab | 8 | 1872 | 2013-11-07
| o2 | 1 | Obact-OZ 10 X10 Tab | 7 | 1260 | 2013-11-08
| o2 | 1 | Omcid - D 10X15 | 12 | 1224 | 2013-11-07
| o3 | 3 | Provel 200gms Powder | 88 | 777 | 2013-11-21
+----------+---------+---------------------------+-----------+--------------+-----------
发票
--------
invoices
--------
+-----------+----------+------------+---------+------------+
| inv_key | order_no | inv_date | cust_id | order_date |
+-----------+----------+------------+---------+------------+
| EDPL-1253 | o4 | 2013-11-07 | 2 | 2013-11-06 |
| EDPL-1253 | o4 | 2013-11-07 | 2 | 2013-11-06 |
| EDPL-1253 | o4 | 2013-11-07 | 2 | 2013-11-06 |
| EDPL-1253 | o1 | 2013-11-07 | 2 | 2013-11-06 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1422 | o2 | 2013-11-08 | 1 | 2013-11-07 |
| EDPL-1423 | o3 | 2013-11-21 | 3 | 2013-11-21 |
| EDPL-1253 | o1 | 2013-11-07 | 2 | 2013-11-06 |
| EDPL-1254 | o4 | 2013-11-19 | 2 | 2013-11-06 |
+-----------+----------+------------+---------+------------+
+---------------------------+---------+------------+
| productname | inv_qty | inv_amount |
+---------------------------+---------+------------+
| Omcid - D 10X15 | 5 | 1000 |
| Obact-OZ 10 X10 Tab | 7 | 900 |
| Obact-200mg 10 X10 Tab | 8 | 720 |
| Calcy - D 10X2X10 | 15 | 540 |
| Pragmox -250mg 20 X10 Cap | 8 | 1864 |
| Provel 200gms Powder | 12 | 372 |
| Obact-200mg 10 X10 Tab | 12 | 1320 |
| Obact-400mg 10 X10 Tab | 7 | 1638 |
| Obact-OZ 10 X10 Tab | 7 | 1260 |
| Omcid - D 10X15 | 12 | 1224 |
| Provel 200gms Powder | 88 | 777 |
| Provel 200gms Powder | 12 | 540 |
| Omcid - D 10X15 | 7 | 320 |
+---------------------------+---------+------------+
我的要求是获取所有与客户相关的订单详情和发票详情，例如
我的查询是
my requirement is get the all cust related order details and invoice details like
my query is
select cust_name,address,contactperson,phone,
group_concat(orders.order_no order by orders.order_no asc) as order_no,
group_concat(product_name) As or_product,
group_concat(order_qty) As order_qty ,
group_concat(if(inv_qty is null,0,inv_qty)) as inv_qty,
group_concat(orders.order_date) As order_date from cust_info
join orders on cust_info.cust_id=orders.cust_id
left outer join invoices on cust_info.cust_id=invoices.cust_id
and orders.product_name=invoices.productname and orders.order_date=invoices.order_date
group by cust_name,orders.order_no;
结果是
（cust_name，地址，联系人，电话，order_no，or_product，order_qty，inv_qty，order_date）价值
（ 'Srinnivas Biotech'，'45 -120/94，Manju shanker Aparments，Bblock'，'先生Shiva Kumar'，'9883883883'，'o3'，'Provel 200gms Powder'，'88'，'88'，'2013-11-21'），
（'M / s Sri baba Enterprices'，'＃11-13-23，Sammetavari街，近ramanaiah冷饮店，Vijayawada-520001'，'Mr.Prasad'，'9346560014'，'o2，o2，o2，o2，o2，o2'，' Provel 200gms Powder，Pragmox -250mg 20 X10 Cap，Omcid - D 10X15，Obact-OZ 10 X10 Tab，Obact-400mg 10 X10 Tab，Obact-200mg 10 X10 Tab'，'15,10,12,7,8,12 '，'12，8,12,0,7,12'，'2013-11-07,2013-11-07,2013-11-07,2013-11-08,2013-11-07,2013-11 -07'），
（'Madhav pharma'，'H No：18-19--99 kaimabad，Warangal-506002'，'Mr.Sammaiah'，'9390115133'，'o1，o1 '，'Calcy - D 10X2X10，Provel 200gms Powder'，'15,12'，'15,12'，'2013-11-06,2013-11-06'），
（' Madhav pharma'，'H No：18-19--99 kaimabad，Warangal-506002'，'Mr.Sammaiah'，'9390115133'，'o4，o4，o4，o4'，'Obact-200mg 10 X10 Tab，Omcid - D 10X15，Omcid - D 10X15，Obact-OZ 10 X10 Tab'，'8,12,12,7'，'8,7,5,7'，'2013-11-06,2013-11-06,2013-11-06,2013-11-06'）< or_product字段中的
包含相同的产品名称，如果产品名称相同，则将其inv_qty添加到组concat中（添加inv_qty然后组concat）但它不起作用我。我使用了以下查询;
result is
(cust_name, address, contactperson, phone, order_no, or_product, order_qty, inv_qty, order_date) VALUES
(' Srinnivas Biotech', '45-120/94, Manju shanker Aparments,Bblock', 'Mr. Shiva Kumar', '9883883883', 'o3', 'Provel 200gms Powder', '88', '88', '2013-11-21'),
('M/s Sri baba Enterprices', '#11-13-23,Sammetavari street,Near ramanaiah cool drink shop,Vijayawada-520001', 'Mr.Prasad', '9346560014', 'o2,o2,o2,o2,o2,o2', 'Provel 200gms Powder,Pragmox -250mg 20 X10 Cap,Omcid - D 10X15,Obact-OZ 10 X10 Tab,Obact-400mg 10 X10 Tab,Obact-200mg 10 X10 Tab', '15,10,12,7,8,12', '12,8,12,0,7,12', '2013-11-07,2013-11-07,2013-11-07,2013-11-08,2013-11-07,2013-11-07'),
('Madhav pharma', 'H No:18-19--99 kaimabad,Warangal-506002', 'Mr.Sammaiah', '9390115133', 'o1,o1', 'Calcy - D 10X2X10,Provel 200gms Powder', '15,12', '15,12', '2013-11-06,2013-11-06'),
('Madhav pharma', 'H No:18-19--99 kaimabad,Warangal-506002', 'Mr.Sammaiah', '9390115133', 'o4,o4,o4,o4', 'Obact-200mg 10 X10 Tab,Omcid - D 10X15,Omcid - D 10X15,Obact-OZ 10 X10 Tab', '8,12,12,7', '8,7,5,7', '2013-11-06,2013-11-06,2013-11-06,2013-11-06')
in or_product field contains same product name, if product name name is same then add their inv_qty inside the group concat(add inv_qty then group concat) but it is not working for me. i used the following query;
select cust_name,address,contactperson,phone,
group_concat(orders.order_no order by orders.order_no asc) as order_no,
group_concat(product_name) As or_product,
group_concat(order_qty) As order_qty ,
group_concat(case when count(invoices.productname)>1 then sum(ifnull(inv_qty,0) else inv_qty end) as inv_qty,
group_concat(orders.order_date) As order_date from cust_info
join orders on cust_info.cust_id=orders.cust_id
left outer join invoices on cust_info.cust_id=invoices.cust_id
and orders.product_name=invoices.productname and orders.order_date=invoices.order_date
group by cust_name,orders.order_no
不使用invoices.productname组我需要记录。无论如何要得到正确的记录请帮帮我。
提前致谢
without using group by invoices.productname i need records. is there anyway to get the correct records please help me.
Thanks in advance
推荐答案
如果您使用汇总函数 [ ^ ]，它不是可以在没有GROUP BY语句的情况下使用它！
您可以尝试使用循环 [ ^ ]，但我不建议你出于几个原因使用它。其中一个是执行时间。
If you use aggregate functions[^], it's not possible to use it withoutGROUP BYstatement!
You can try to achieve the same using loops[^], but i would not recommend you to use it for several reasons. One of them is a time of execution.
这篇关于如何在mysql中不使用group by条件获取记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
