SQL连接条件

发布于 2024-11-17 05:12:18 字数 1013 浏览 2 评论 0原文

我想为每个身高超过一米的猫牧民排成一行的三张表，

cats
=============
id
cat_herder_id
size
color
birthday

collars
=============
id
cat_id
diameter
color
material

cat_herders
=============
id
name
age
height

其中包含属于该牧民的二月份出生的橙色、黑色项圈的猫的数量以及所有该牧民的猫的计数橙色和蓝色领子属于每个牧民，我将如何进行此类查询。

我认为我不能简单地在 where 语句中指定，因为我的计数似乎已关闭，我按 cat_herders.id 进行分组

编辑：到目前为止我所拥有的内容的较少清理、较少抽象版本：

SELECT company.tblusers.first_name, company.tblusers.last_name, company.tblusers.userid, SUM(db.tasks.estimated_nonrecurring+db.tasks.estimated_recurring), COUNT(sugarcrm2.ncr_ncr.id),  
SUM(db.batch_log.time_elapsed) FROM company.tblusers 
INNER JOIN db.batch_log ON company.tblusers.userid = db.batch_log.userid 
INNER JOIN db.tasks ON db.batch_log.batch_id = db.tasks.batch_id
INNER JOIN sugarcrm2.ncr_ncr ON company.tblusers.first_name + " " + company.tblusers.first_name = sugarcrm2.ncr_ncr.employee
WHERE  departmentid = 8 AND DATE(db.batch_log.start_time) =  DATE(NOW()) GROUP BY userid

原文

Three tables

cats
=============
id
cat_herder_id
size
color
birthday

collars
=============
id
cat_id
diameter
color
material

cat_herders
=============
id
name
age
height

I want to get a row for each cat herder that is over a meter tall with a count of the number of cats that were born in February that are orange with black collars that belong to that herder and a count of all the cats that are orange with blue collars the belong to each herder, how would I go about doing a query of that sort.

I don't think I can simply specify in the where statement because my count appears to be off, I am grouping by cat_herders.id

EDIT: Less sanitized, less abstracted version of what I have so far:

SELECT company.tblusers.first_name, company.tblusers.last_name, company.tblusers.userid, SUM(db.tasks.estimated_nonrecurring+db.tasks.estimated_recurring), COUNT(sugarcrm2.ncr_ncr.id),  
SUM(db.batch_log.time_elapsed) FROM company.tblusers 
INNER JOIN db.batch_log ON company.tblusers.userid = db.batch_log.userid 
INNER JOIN db.tasks ON db.batch_log.batch_id = db.tasks.batch_id
INNER JOIN sugarcrm2.ncr_ncr ON company.tblusers.first_name + " " + company.tblusers.first_name = sugarcrm2.ncr_ncr.employee
WHERE  departmentid = 8 AND DATE(db.batch_log.start_time) =  DATE(NOW()) GROUP BY userid

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

亣腦蒛氧 2024-11-24 05:12:18

注意：根据您的 RDBMS 和 cats.birthday 列的数据类型，您可能需要调整此查询以提取出生月份，但这为您提供了总体思路。

SELECT ch.name,
       SUM(CASE WHEN c.color = 'orange' AND cl.color = 'black' AND c.birthday = 'February' THEN 1 ELSE 0 END) AS OrangeCatsBlackCollarsFeb,
       SUM(CASE WHEN c.color = 'orange' AND cl.color = 'blue' THEN 1 ELSE 0 END) AS OrangeCatsBlueCollars 
    FROM cat_herders ch
        LEFT JOIN cats c
            INNER JOIN collars cl
                ON c.id = cl.cat_id
            ON ch.id = c.cat_herder_id
    WHERE ch.height > 1 /* in meters */
    GROUP BY ch.name

Note: Depending on your RDBMS and the datatype of the cats.birthday column, you'll probably need to adjust this query to extract the birth month, but this gives you the general idea.

SELECT ch.name,
       SUM(CASE WHEN c.color = 'orange' AND cl.color = 'black' AND c.birthday = 'February' THEN 1 ELSE 0 END) AS OrangeCatsBlackCollarsFeb,
       SUM(CASE WHEN c.color = 'orange' AND cl.color = 'blue' THEN 1 ELSE 0 END) AS OrangeCatsBlueCollars 
    FROM cat_herders ch
        LEFT JOIN cats c
            INNER JOIN collars cl
                ON c.id = cl.cat_id
            ON ch.id = c.cat_herder_id
    WHERE ch.height > 1 /* in meters */
    GROUP BY ch.name

回复收藏 0 原文

~没有更多了~