计算1列中的项目并将它们分成两列

I have a two tables, data and agentdata.

我有两个表,数据和agentdata。

data (agentid, SimCardNumber, ProductName, Agentemail)

数据(agentid,SimCardNumber,ProductName,Agentemail)

agentdata (id, agentid, ProductName)

agentdata(id,agentid,ProductName)

currently i deal only two products and i want to view this list as group by agentemail

目前我只处理两种产品,我想通过agentemail将此列表视为组

agentemail    |  Product1  |  Total1   |  Product2  |  Total2
-----------------------------------------------------------
abc@gmail.com |  ball      |   5       |  bat       |  0

i tried this query but i have problem to show two products and their count side by side.

我试过这个查询,但我有问题,以显示两个产品和他们的数量并排。

SELECT a.agentid,  d.Agentemail, a.ProductName, COUNT(a.ProductName) AS Total  
FROM  data AS d, agentdata AS a 
WHERE d.agentid=a.agentid
GROUP BY a.ProductName

2 个解决方案

#1

SQL isn't built to handle side-by-side things like that. It works on rows, you want it to display columns.

SQL不是为了处理这样的并排事务而构建的。它适用于行,您希望它显示列。

Your original query should GROUP BY agentid, ProductName to separate out the agents from the product totals.

您的原始查询应使用GROUP BY agentid,ProductName将代理与产品总计分开。

If you know that you only have two product and no more, you can write a query to display just those two products side-by-side. If you add a third product, it will not include it.

如果您知道只有两个产品而不是更多,那么您可以编写一个查询来并排显示这两个产品。如果您添加第三个产品,则不会包含它。

SELECT a.agentid,  d.Agentemail, 
    'ball' AS Product1, 
    SUM(IF(a.ProductName = 'ball', 1, 0)) AS Total1,  
    'bat' AS Product2, 
    SUM(IF(a.ProductName = 'bat', 1, 0)) AS Total2,  
FROM  data AS d, agentdata AS a 
WHERE d.agentid=a.agentid
GROUP BY agentid

This sort of query is OK for doing quick checks on data, but your front-end should use a more solid query and build the table there.

这种查询可以快速检查数据,但是您的前端应该使用更可靠的查询并在那里构建表。

#2

data (agentid, SimCardNumber, ProductName, Agentemail)

agentdata (id, agentid, ProductName)

You should REALLY think about your table layout. if agentid == agentid and ProductName == ProductName, this layout is against everything a normalized database would store - and therefore no queries that actually make sence are possible...

你应该真正考虑你的表格布局。如果agentid == agentid和ProductName == ProductName,则此布局针对规范化数据库将存储的所有内容 - 因此不存在实际可能存在的查询...

do you want a 1:1 relation? (One Agent, one Product): Then you dont need the agentdata-table at all.

你想要1:1的关系吗? (一个代理,一个产品):那么你根本不需要agentdata-table。

do you want a 1:n relation? (One Agent, multipe Products): Then you don't need the ProductName in the data-table.

你想要一个1:n的关系吗? (One Agent,multipe Products):然后您不需要数据表中的ProductName。

do you want a n:1 relation? (Multiple Agents, One Product): Then you don't need the agentid in the agentdata-table.

你想要一个n:1的关系吗? (多个代理,一个产品):然后您不需要agentdata-table中的agentid。

do you want a n:n relation? (Multiple Agents, Multiple Products): Then you need to remove ProductName from the data-table, remove agentid from the agentdata-table and create a Mapping Table with ProductName and agentid on which you finally could join your data- and agentdata-table.

你想要一个n:n关系吗? (多个代理,多个产品):然后,您需要从数据表中删除ProductName,从agentdata表中删除agentid,并创建一个带有ProductName和agentid的映射表,最终可以在其上加入data- and agentdata-table。

With this layout - and without knowing what data is stored where, it's impossible to give an answer that really helps you with your problem.

有了这种布局 - 并且不知道哪些数据存储在哪里,就不可能给出真正帮助解决问题的答案。

#1