下面是用于显示结果的sql查询
SELECT pd.SPDNO,bike_details.Cost, pd.Month_Name,pd.Company_Name,
bike_details.Product,pd.Quantity,pd.Bill_Amount,pd.Due_Date
FROM payment_details pd INNER JOIN bike_details ON pd.SPDNO = bike_details.SPDNO WHERE pd.SPDNO='5000'
以下是输出
在图像产品列有10辆自行车,颜色是4红色和6黑色,现在我想显示单行记录基于不同的自行车颜色
我还使用了group by,但它没有给出所需的输出
预期结果应该是这样的
Order ID Company Name Product Quantity Per Bike Cost Month Total Cost Due Date
5000 Memon Motors SUPER STAR (SS-70cc)M.C Red(4),
SUPER STAR (SS-70cc)M.C Black(6) 10 47000 June 470000 2020-07-01
如有任何帮助,我们将不胜感激& 提前致谢
您可以按select列分组,并在末尾放一个计数,以获得(4)和(6)
SELECT pd.SPDNO,bike_details.Cost, pd.Month_Name,pd.Company_Name,
GROUP_CONCAT(bike_details.Product) + '('+COUNT(*)+')' AS 'SomeColumnName' ,pd.Quantity,pd.Bill_Amount,pd.Due_Date
FROM payment_details pd INNER JOIN bike_details ON pd.SPDNO = bike_details.SPDNO WHERE pd.SPDNO='5000
group by pd.SPDNO,bike_details.Cost, pd.Month_Name,pd.Company_Name,
pd.Quantity,pd.Bill_Amount,pd.Due_Date