有什么可以帮助我创建查询以得到正确的结果?

This is my table I need to get results like:

这是我的表格，我需要得到如下结果:

in 560038 two hospitals, one temple, zero masjid, zero company, one church and 
in 560049 one schools, one masjid, one company, zero temple, zero hospitals, and 
in 562129 one high school, zero hospitals, zero company, zero masjid , zero temple. 

604=hospital,
1306=temple,
1302=church,
204=school,
1305=masjid,
102=company.

Is it possible to get from a single query in mysql? Here I can give city_id only. Can any one help me?

是否可以从mysql中的单个查询中获取?这里我只能给city_id。有人能帮我吗?

id  |cat_id|poi_id |addr                    |pincode|city_id 
1   |6     |604     |Amrutha  Hospital      |560038 |284 
2   |13    |1306    |Temple             |560038 |284 
3   |13    |1302    |Resurrection Church    |560038 |284 
4   |2     |204     |Lake Montfort School   |560049 |284 
5   |6     |604     |Garden City Hospital   |560038 |284 
6   |13    |1305    |Bilal Masjid           |560049 |284 
7   |1     |102     |Grindwell Limited      |560049 |284 
8   |2     |204     |Citizen High School    |562129 |284

4 个解决方案

#1

You can use CASE expressions within COUNT() to conditionally aggregate poi_ids to differentiate the counts, grouped by pincode.

您可以在COUNT()中使用大小写表达式来有条件地聚合poi_id来区分计数，按pincode分组。

You want:

你想要的:

SELECT   pincode,
         COUNT(CASE poi_id WHEN 604  THEN 1 END) AS hospital_cnt,
         COUNT(CASE poi_id WHEN 1306 THEN 1 END) AS temple_cnt,
         COUNT(CASE poi_id WHEN 1302 THEN 1 END) AS church_cnt,
         COUNT(CASE poi_id WHEN 204  THEN 1 END) AS school_cnt,
         COUNT(CASE poi_id WHEN 1305 THEN 1 END) AS masjid_cnt,
         COUNT(CASE poi_id WHEN 102  THEN 1 END) AS company_cnt
FROM     tbl
GROUP BY pincode

Edit: As per your comments, you can get a row format of the counts of each possible poi_id per pincode:

编辑:根据您的评论，您可以获得每个pincode中每个可能的poi_id的行格式:

SELECT     a.pincode,
           b.poi_id,
           COUNT(c.pincode) AS cnt
FROM       (SELECT DISTINCT pincode FROM tbl) a
CROSS JOIN poi b
LEFT JOIN  tbl c ON a.pincode = c.pincode AND b.poi_id = c.poi_id
GROUP BY   a.pincode, b.poi_id

Where poi is the name of the table that poi_id links to. Make sure the column names are consistent too.

poi是poi_id链接到的表的名称。确保列名也是一致的。

If you still want one row per pincode, you can use GROUP_CONCAT to bring the counts all into one delimited string:

如果您仍然希望每个pincode都有一行，您可以使用GROUP_CONCAT将所有计数合并到一个带分隔符的字符串中:

SELECT   a.pincode,
         GROUP_CONCAT(CONCAT(a.poi_id, ': ', a.cnt) SEPARATOR ' / ') AS poi_cnts
FROM     (
         SELECT     a.pincode,
                    b.poi_id,
                    COUNT(c.pincode) AS cnt
         FROM       (SELECT DISTINCT pincode FROM tbl) a
         CROSS JOIN poi b
         LEFT JOIN  tbl c ON a.pincode = c.pincode AND b.poi_id = c.poi_id
         GROUP BY   a.pincode, b.poi_id
         ) a
GROUP BY a.pincode

#2

select pincode, addr, count(id) from YourTable group by pincode, addr

#3

I think this is what you are looking for:

我想这就是你们想要的:

SELECT pincode, poi_id, COUNT(1) AS place_cnt
FROM table_name
GROUP BY pincode, poi_id;

#4

-1

If I've understood correctly, you want a resultset where each row is the pincode followed by the numver of each type of point of interest.

如果理解正确，您需要一个resultset，其中每一行都是pincode，然后是每种类型的感兴趣点的数字。

The target results you specified look like the need to be a long, natural english string. I'm not going to get you that far but I will give you something that you can work with in code.

你指定的目标结果看起来像是一个长而自然的英语字符串。我不会给你们讲那么多但是我会给你们一些你们可以在代码中使用的东西。

Here's the end result that you'll end up with:

最后的结果是:

pincode | hospitals | temples | masjids | companies | churches | schools
560038  |     2     |    1    |    0    |     0     |    1     |    0
560049  |     0     |    0    |    1    |     1     |    0     |    1
562129  |     0     |    0    |    0    |     0     |    0     |    1

NOTE: I don't think requiring the results in this way is ideal. If new types of points of interest are added later you'll have to rewrite the query. But since this is the question, here's the answer:

注意:我不认为以这种方式要求结果是理想的。如果稍后添加了新的感兴趣点类型，您将不得不重写查询。但既然这是一个问题，答案是:

There's two ways to do this and both use sub queries that do count(*) for each type of point of interest. The first option is the embed the sub queries in the SELECT part of the query. The second option is to make derived tables out of the sub queries and join them together.

有两种方法可以做到这一点，它们都使用对每种类型的感兴趣点都进行计数(*)的子查询。第一个选项是将子查询嵌入查询的SELECT部分。第二个选项是将派生表从子查询中提取出来，并将它们连接在一起。

Here's option 1:

选项1:

SELECT
mainTbl.pincode,
(SELECT count(*) FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id AND poi_id = 604) as hospitals,
(SELECT count(*) FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id AND poi_id = 1306) as temples,
(SELECT count(*) FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id AND poi_id = 1305) as masjids,
(SELECT count(*) FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id AND poi_id = 1302) as churches,
(SELECT count(*) FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id AND poi_id = 204) as schools,
(SELECT count(*) FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id AND poi_id = 102) as companies
FROM myTable mainTbl
WHERE mainTbl.city_id = ?

And option2:

和option2:

SELECT
mainTbl.pincode,
hospitalsTbl.hospitals,
templesTbl.temples,
masjidsTbl.masjids,
churchesTbl.churches,
schoolsTbl.schools,
companiesTbl.companies

FROM myTable mainTbl
JOIN (SELECT count(*) as hospitals, pincode FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id  AND poi_id = 604) hospitalsTbl ON hospitalsTbl.pincode = mainTbl.pincode
JOIN (SELECT count(*) as temples, pincode FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id AND poi_id = 1306) templesTbl ON templesTbl.pincode = mainTbl.pincode
JOIN (SELECT count(*) as masjids, pincode FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id AND poi_id = 1305) masjidsTbl ON masjidsTbl.pincode = mainTbl.pincode
JOIN (SELECT count(*) as churches, pincode FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id AND poi_id = 1302) churchesTbl ON churchesTbl.pincode = mainTbl.pincode
JOIN (SELECT count(*) as schools, pincode FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id AND poi_id = 204) schoolsTbl ON schoolsTbl.pincode = mainTbl.pincode
JOIN (SELECT count(*) as companies, pincode FROM myTable WHERE pincode = mainTbl.pincode AND city_id = mainTbl.city_id AND poi_id = 102) companiesTbl ON companiesTbl.pincode = mainTbl.pincode
WHERE mainTbl.city_id = ?

As you can see the queries are quite verbose. It's much better to have something like this:

正如您所看到的，查询非常冗长。最好有这样的东西:

SELECT count(*) as num_poi, poi_id, pincode
FROM myTable
WHERE city_id = ?
GROUP BY poi_id, pincode

and then build your one line rows in code.

然后用代码构建一行。

#1