php - Yii 条件条件不工作 SQL 错误

Question

$criteria->condition .= 'UserID = '.$userId.' UNION
(
    SELECT * FROM display WHERE 
    UserID IN 
    (
        SELECT DISTINCT UserID FROM lkusergroup WHERE 
        GroupID IN
        (
            SELECT GroupID FROM lkusergroup WHERE UserID = '.$userId.'
        )
    )
)';

我正在尝试执行上面的代码但出现以下错误

    CDbCommand failed to execute the SQL statement: SQLSTATE[21000]: 
Cardinality violation: 1222 The used SELECT statements have a different number of columns. The SQL statement executed was:
SELECT COUNT(*) FROM `display` `t` WHERE UserID = 2 UNION
        (
        SELECT * FROM display WHERE
        UserID IN
        (
        SELECT DISTINCT UserID FROM lkusergroup WHERE
        GroupID IN
        (
        SELECT GroupID FROM lkusergroup WHERE UserID = 2
        )
        )
    ) 

为什么查询生成 count(*) 这是实际问题。我试过可能的方法来解决这个错误 bt 没有任何建议？

Answer 1

您还需要在下面的行中添加计数函数:

SELECT * FROM display WHERE 
    UserID IN 

你的代码必须是这样的:

$criteria->condition .= 'UserID = '.$userId.' UNION
(
    SELECT count(*) FROM display WHERE 
    UserID IN 
    (
        SELECT DISTINCT UserID FROM lkusergroup WHERE 
        GroupID IN
        (
            SELECT GroupID FROM lkusergroup WHERE UserID = '.$userId.'
        )
    )
)';

在 UNION 时，必须从查询中返回相同数量的列。

已编辑:

你的查询必须是这样的:

SELECT COUNT(*), * FROM display WHERE
    UserID IN
    (
       SELECT DISTINCT UserID FROM lkusergroup WHERE
        GroupID IN
        (
           SELECT GroupID FROM lkusergroup WHERE UserID = 2
        )
    )