加入多行数据进行查询(Join multiple rows of data to query)

我正试图将多行数据加入到一个sql查询中。

我的数据库由几个表组成： clients ， client_site_ip和ip_accounting 。

client_site_ip表具有以下行： siteid ， userid ， site_name和site_ip 。

ip_accounting表具有以下行： id ， src_address ， dst_address ， bytes和timeanddate 。

clients表包含以下行： userid ， username ......和ipaddress

我想让客户拥有多个IP地址，所以我创建了表client_site_ip

我目前有这样一个查询，用逗号分隔IP地址和所有内容，但需要用新数据行而不是逗号分隔：

SELECT clients.userid, clients.username, clients.datapackage, client_site_ip.userid, GROUP_CONCAT(client_site_ip.site_name) AS sitenames, GROUP_CONCAT(client_site_ip.site_ip) AS ipaddresses, data_packages.packagename, data_packages.speedlimit, data_packages.threshold, data_packages.accountingdays, data_packages.throttlelimit, data_packages.datalimitamount FROM clients JOIN client_site_ip ON clients.userid = client_site_ip.userid JOIN data_packages ON clients.datapackage = data_packages.package_id GROUP BY clients.userid

我还需要更新我的同步查询，当前脚本如下所示：

SELECT ip_address ,SUM(upload_bytes) as upload_bytes ,SUM(download_bytes) as download_bytes ,sum(upload_bytes + download_bytes) as totalbytes ,package_id ,username ,userid ,networkaccess ,packagename ,speedlimit ,threshold ,throttlelimit ,extendeddata ,datalimitamount ,accountingdays FROM ( (SELECT ip_accounting.src_address as ip_address ,SUM(ip_accounting.bytes) AS upload_bytes ,0 as download_bytes ,clients.username ,clients.userid ,clients.networkaccess ,clients.extendeddata ,data_packages.package_id ,data_packages.packagename ,data_packages.speedlimit ,data_packages.threshold ,data_packages.throttlelimit ,data_packages.datalimitamount ,data_packages.accountingdays FROM ip_accounting join clients on clients.ipaddress = ip_accounting.src_address join data_packages on data_packages.package_id = clients.datapackage WHERE dst_address NOT BETWEEN INET_NTOA('192.168.0.1') AND INET_NTOA('192.168.255.254') and timeanddate BETWEEN SUBDATE(CURRENT_TIMESTAMP(), INTERVAL data_packages.accountingdays DAY) AND CURRENT_TIMESTAMP() GROUP BY src_address) UNION ALL (SELECT ip_accounting.dst_address as ip_address ,0 AS upload_bytes ,SUM(ip_accounting.bytes) as download_bytes ,clients.username ,clients.userid ,clients.networkaccess ,clients.extendeddata ,data_packages.package_id ,data_packages.packagename ,data_packages.speedlimit ,data_packages.threshold ,data_packages.throttlelimit ,data_packages.datalimitamount ,data_packages.accountingdays FROM ip_accounting join clients on clients.ipaddress = ip_accounting.dst_address join data_packages on data_packages.package_id = clients.datapackage WHERE src_address NOT BETWEEN INET_NTOA('192.168.0.1') AND INET_NTOA('192.168.255.254') and timeanddate BETWEEN SUBDATE(CURRENT_TIMESTAMP(), INTERVAL data_packages.accountingdays DAY) AND CURRENT_TIMESTAMP() GROUP BY dst_address) ) a GROUP BY ip_address ORDER BY INET_ATON(ip_address)

I am trying to join multiple rows of data onto a sql query.

My database consists of a few tables: clients, client_site_ip, and ip_accounting.

client_site_ip table has the following rows: siteid, userid, site_name and site_ip.

ip_accounting table has the following rows: id, src_address, dst_address, bytes and timeanddate.

clients table has the following rows: userid, username ...... and ipaddress

I am wanting clients to have multiple IP addresses so I have created the table client_site_ip

I currently have this query that separates the IP addresses and everything by a comma but need it separated by rows of new data not commas:

I also need my synchronization query updated, the current script looks like:

最满意答案

如果你想为每个site_ip和site_name分开行，你也可以在这些列上进行分组

SELECT clients.userid, clients.username, clients.datapackage, client_site_ip.userid, client_site_ip.site_name AS sitenames, client_site_ip.site_ip AS ipaddresses, data_packages.packagename, data_packages.speedlimit, data_packages.threshold, data_packages.accountingdays, data_packages.throttlelimit, data_packages.datalimitamount FROM clients JOIN client_site_ip ON clients.userid = client_site_ip.userid JOIN data_packages ON clients.datapackage = data_packages.package_id GROUP BY clients.userid, client_site_ip.site_name, client_site_ip.site_ip

到你的同步查询为止，我不相信你需要任何更新，因为所有连接已经在ipaddress等上，而不是在group_concat

If you want separate rows for each site_ip and site_name, you can group on these columns as well

As far your synchronization query goes, I don't believe you'll need any update since all joins are already on ipaddress etc. and not on group_concat

更多推荐