mysql concat之后在哪里？

qq24tv8q 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(351)

我有一个管理出勤的查询。我想添加一个功能，显示谁已经支付了一个月，谁没有。它几乎奏效了。

SELECT
    students.sid,
    students.name,
    students.day,
    students.times,
    students.days,
    CONCAT(payments.year,'-', payments.forMonth) AS pdate
FROM students
LEFT JOIN payments
    ON students.sid = payments.sid
WHERE
    students.Active = 'Yes' AND
    students.day LIKE 'Tue%' AND
    payments.date = (SELECT date FROM payments WHERE students.sid = payments.sid AND
                     payments.payfor = 'tuition' ORDER BY payments.pid DESC LIMIT 1 )

主要的问题是（selectdatefrom）子查询。concat在payments子查询集中的payments.payfor限制器之前获取它从payments表获得的第一个值。所以，在下面的表片段中，我得到的是sk1条目，而不是dec的正确学费

pid     sid  amt    payfor  forMonth    year    date
1076    69  7000    tuition Dec         2017    2017-12-17
1074    69  4000     sk1    Sep         2017    2017-12-17
1046    69  7000    tuition Nov         2017    2017-11-23

有没有一种方法可以在concat（payments.year，'-'，payments.formonth）之前或通过concat获得payments.date子查询？

mysql subquery concat

来源：https://stackoverflow.com/questions/48051041/mysql-concat-after-where

2条答案

按热度按时间

ghhkc1vu1#

您应该在related on子句中移动与左联接表相关的条件，否则，如果在where条件作为内部联接工作，并且不返回相关行

SELECT students.sid, students.name, students.day, students.times, students.days, 
  CONCAT(payments.year,'-', payments.forMonth) AS pdate 
  FROM students 
  LEFT JOIN payments ON students.sid = payments.sid  
        AND payments.date = (SELECT date 
          FROM payments 
          WHERE students.sid = payments.sid 
          AND payments.payfor = 'tuition' 
          ORDER BY payments.pid DESC LIMIT 1 
        )
  WHERE students.Active = 'Yes' 
  AND students.day LIKE 'Tue%'

如果你只想要学费，你也应该过滤这个值

SELECT students.sid, students.name, students.day, students.times, students.days, 
  CONCAT(payments.year,'-', payments.forMonth) AS pdate 
  FROM students 
  LEFT JOIN payments ON students.sid = payments.sid  
        AND payments.date = (SELECT date 
          FROM payments 
          WHERE students.sid = payments.sid 
          AND payments.payfor = 'tuition' 
          ORDER BY payments.pid DESC LIMIT 1 
        ) 
        AND payments.payfor='tuition'

  WHERE students.Active = 'Yes' 
  AND students.day LIKE 'Tue%'

赞(0）回复(0）举报 2021-06-21

hec6srdp2#

好的，我知道了。我补充道

AND payments.payfor = 'tuition'

之后

students.Active = 'Yes' AND
students.day LIKE 'Tue%'

在我最初的查询中。然后，一切按需运转。

赞(0）回复(0）举报 2021-06-21

我来回答

mysql concat之后在哪里？

2条答案

相关问题

热门标签

最新问答