duane9322 2015-05-14 10:46
浏览 64

选择NOT EXISTS和CASE的随机行

I have three tables in my database for questions, answers and viewed:

Questions:

---------------------------------
| ID | category | title         |
---------------------------------
| 1  | 1        | What is this? |
---------------------------------
| 2  | 1        | Who is this?  |
---------------------------------

And so on: category is a category id from a different table, but thats not important.

Then I have answers:

-------------------------------------------
| ID | question | answer | user | date    |
-------------------------------------------
| 1  | 1        | Blue   | 25   | 1.1.15  |
-------------------------------------------
| 2  | 1        | Red    | 18   | 2.2.15  |
-------------------------------------------
| 3  | 2        | Brad   | 25   | 1.1.15  |
-------------------------------------------

Where question is the id from questions table and user is user_id

Then I have viewed:

-----------------------------------
| ID | category | question | user |
-----------------------------------
| 1  | 1        | 1        | 25   |
-----------------------------------
| 2  | 1        | 1        | 18   |
-----------------------------------
| 3  | 1        | 2        | 25   |
-----------------------------------

This will keep track of which questions users have seen, so they don't see the same question twice. And the point is that a user can give their answer that another user have given, so that won't be stored in answers table.

I have tried with this MYSQL-code:

SELECT Q.ID, Q.category, Q.title, A.ID, A.answer
FROM questions Q, answers A
WHERE
NOT EXISTS (
    SELECT *
    FROM category_answered S
    WHERE Q.ID = S.question AND S.user = '$user' AND S.category = '$category'
)
AND
CASE
WHEN Q.category = '$category' THEN Q.ID = A.question
ELSE Q.category = '$category'
END
ORDER BY RAND() LIMIT 1

This list everything except the question that matches. So ORDER BY RAND() LIMIT 1 is not working.

Any help will be gladly appreciated

  • 写回答

0条回答 默认 最新

    报告相同问题?

    悬赏问题

    • ¥15 虚幻5 UE美术毛发渲染
    • ¥15 CVRP 图论 物流运输优化
    • ¥15 Tableau online 嵌入ppt失败
    • ¥100 支付宝网页转账系统不识别账号
    • ¥15 基于单片机的靶位控制系统
    • ¥15 真我手机蓝牙传输进度消息被关闭了,怎么打开?(关键词-消息通知)
    • ¥15 装 pytorch 的时候出了好多问题,遇到这种情况怎么处理?
    • ¥20 IOS游览器某宝手机网页版自动立即购买JavaScript脚本
    • ¥15 手机接入宽带网线,如何释放宽带全部速度
    • ¥30 关于#r语言#的问题:如何对R语言中mfgarch包中构建的garch-midas模型进行样本内长期波动率预测和样本外长期波动率预测