一次和循环结果SQL查询两个表JSON对象
我不能完全确定什么,我期待在这里,所以道歉,如果这已经涵盖在这里,我不知道我需要寻找!一次和循环结果SQL查询两个表JSON对象
我有一个名为 “位置” 表中的MySQL数据库,看起来有点像这样
id | name | other parameters
1 | shop1 | blah
2 | shop2 | blah
等
和客户的表查询
id | customer | department
1 | john | shop2
2 | Joe | shop2
3 | James | shop1
4 | Sue | shop2
等
我想查询此并返回一个JSON对象,看起来像这样
{"location":"shop1","queryCount":"1"},{"location":"shop2","queryCount":"3"}
位置表可以随时添加,显然客户查询将会是,因此都需要动态查询。
我想这通过一个简单的SELECT name from locations
查询得到的位置列表,把它变成一个数组,然后通过循环如下:
For i = UBound(listofLocations) To 0 Step -1
locations.id = listofLocations(i)
locations.queryCount= RESULT OF: "SELECT COUNT(id) as recordCount from queries WHERE department=listofLocations(i)"
objectArray.Add(locations)
Next
这工作,但它是低效调用数据库通过循环,我如何避免这种情况?
感谢
的低效是因为你使用嵌套查询,
你应该使用LEFT JOIN,你只需要单个查询
这里是SQL: -
select l.name, count(*) as recordCount
from Locations as l
left join customer as c
on l.name = c.department
group by l.id;
而你的模式不是很优化。
您的顾客的模式应该是
id, customer, location_id <-- using name is redundant,
<-- which should represent in ID (location ID)
首先,我要建议您的“部门”现场进行更换。如果您在地点表中更改名称的拼写,那么您的关系会中断。
相反,使用id从位置表,并成立了一个外键引用。
但是这是一个问题。使用当前的结构,这就是我在做SQL ...
SELECT
location.name,
COUNT(queries.id) AS count_of_queries
FROM
locations
LEFT JOIN
queries
ON queries.department = locations.name
GROUP BY
location.name
使用LEFT JOIN,你让每一个位置,即使它没有查询保证。
使用COUNT(queries.id)而不是COUNT(*)给出了0是否有查询表中没有相关记录。
然后,您可以通过循环结果集一个查询,而不是循环多次查询,并建立您的JSON字符串。
它是可能在SQL中构建字符串,但这通常被认为是不好的做法。更好地保持你关于数据的SQL,以及你的php /关于处理和呈现它的任何内容。
试试这个:
SELECT T1.name, (SELECT COUNT(*) FROM queries AS T2 WHERE T2.department = T1.name) AS number FROM locations AS T1;
我同意你的计划是不妥当的,你应该通过ID号和部门,叫不上名字
我认为你只是在寻找 “GROUP BY”
SELECT
department_id as location,
COUNT(id) as querycount
FROM
(table of customer queries)
GROUP BY
department_id
至于数据库的结构......如果可能的话,我会改变的表位:
Location:
id | name | other parameters
Customer:
id | name | whatever
table_of_customer_queries:
id | customer_id | location_id
1 | 2 | 2
2 | 4 | 2
3 | 2 | 1
4 | 1 | 2
GROUP BY
只会给你那些具有查询部门的结果。如果你想要所有的部门,前面提到的LEFT JOIN
选项是要走的路。
首先,如果你Locations
表真正存储Department
信息,将其重命名为这样。然后,改变Customer.department
是一个FK参考Locations.id
列(并更名为department_id
),而不是name
(少了这么多得到错误的)。这可能会也可能不会给你提升性能;但是,请记住,在数据库中的数据是不是真的意味着是人可读 - 它的意思是程序可读。
在这两种情况下,查询可以写成这样:
SELECT a.name, (SELECT COUNT(b.id)
FROM Customer as b
WHERE b.department_id = a.id) as count
FROM Location as a
如果你不改变你的模式,你的答案是通过计数查询的数量到每个位置条款做一组。
创建表像您一样的:
create table #locations (id integer, name varchar(20), other text)
create table #queries (id integer, customer varchar(20), department varchar(20))
insert into #locations (id, name, other) values (1, 'shop1', 'blah')
insert into #locations (id, name, other) values (2, 'shop2', 'blah')
insert into #queries (id, customer, department) values (1, 'john', 'shop2')
insert into #queries (id, customer, department) values (2, 'Joe', 'shop2')
insert into #queries (id, customer, department) values (3, 'James', 'shop1')
insert into #queries (id, customer, department) values (4, 'Sue', 'shop2')
查询数据:
select
l.name as location,
count(q.id) as queryCount
from #locations as l
left join #queries as q on q.department = l.name
group by l.name
order by l.name
结果:
location queryCount
-------------------- -----------
shop1 1
shop2 3