我需要创建Mysql案例并基于这些案例我必须应用不同的搜索查询但我得到错误 [英] I Need To Create Mysql Case And On The Based On Those Case I Have To Apply Different Search Query But I Get Error

问题描述

我有父表投诉（complaint_id，linked_cat）

3儿童表

3.1 complaint_staff（complaint_id，title，priority）

3.1.1 complaint_staff的孩子，即complaint_staff_answer（complaint_id，answers）

3.2 complaint_product（complaint_id，product_name，issue）

3.3 complaint_other（complaint_id，issue，priority）



现在我想从子表中获取记录但是基于案例它依赖于linked_cat的值，我认为问题可以通过使用案例来解决



sql查询：

I have a parent table complaint(complaint_id, linked_cat)
3 child table
3.1 complaint_staff(complaint_id, title, priority)
3.1.1 child of complaint_staff i.e. complaint_staff_answer(complaint_id, answers)
3.2 complaint_product(complaint_id, product_name, issue)
3.3 complaint_other(complaint_id, issue, priority)

now i want to fetch record from child table but on the basis of cases it depends on value of linked_cat, i think the problem can be solve by use of cases

sql query:

select complaints.linked_cat from complaints
case  when complaints.linked_cat = '1' then 
            SELECT 
            complaints_staff.*
            complaints_staff_answers.cat_id 
            FROM
            complaints,
            complaints_staff,
            complaints_staff_answers
            where 
            complaints.complaint_id = complaints_staff.complaint_id 
            and complaints_staff.complaint_id = complaints_staff_answers.complaint_id

case when complaints.linked_cat = '2' then
            select 
            complaints.user_id,complaints.submitted_on,
            complaints_product.*
            from complaints_product, complaints
            where complaints_product.complaint_id = complaints.complaint_id
else
            complaints.user_id,complaints.submitted_on,
            complaints_other.*
            from complaints_other, complaints
            where complaints_other.complaint_id = complaints.complaint_id
end;

它通过错误：case在这个位置无效输入



我怎么能解决这些问题??????

it through error: case is not valid input at this position

how could i solve these problem??????

推荐答案

Quote：

case在此位置无效输入

case is not valid input at this position

是至少由

select complaints.linked_cat from complaints
case  when complaints.linked_cat = '1' then

应该有 WHERE 或; 或这些行之间的东西。



但是从整体上看查询它永远不会像查询那样工作 - 也许是sql脚本（它仍然很难）。



如果你在存储过程中这样做，那么你需要捕获 complaints.linked_cat 您感兴趣，该查询需要 WHERE 条款。



或者，如果你是尝试显示不同的数据，具体取决于每行的类别，那么您将需要加入所有表并将CASE作为选择的一部分包括在内

there should be a WHERE or a ; or something between those lines.

But looking at the "query" as a whole it will never work as a query - as sql script perhaps (it will still struggle).

If you are doing this in a stored procedure then you need to capture the complaints.linked_cat you are interested in and that query will need a WHERE clause.

Or, if you are trying to display different data depending on what the category is for each row then you will need to join all of the tables and include the CASE as part of the select e.g.

SELECT             complaints_staff.*, complaints_staff_answers.cat_id
SELECT AName CASE when  complaints.linked_cat = '1' then complaints_staff.Name WHEN  complaints.linked_cat = '2' ELSE complaints.Name END,
-- other stuff in here
FROM
complaints
LEFT OUTER JOIN complaints_staff ON complaints.complaint_id = complaints_staff.complaint_id
LEFT OUTER JOIN complaints_staff_answers ON complaints_staff.complaint_id = complaints_staff_answers.complaint_id
LEFT OUTER JOIN complaints_product ON complaints_product.complaint_id = complaints.complaint_id
LEFT OUTER JOIN complaints_other ON complaints_other.complaint_id = complaints.complaint_id

您需要放弃使用。* 同样 - 明确命名你需要的列。



寻找查询之间的共性 - 什么列你需要所有这些，哪些列是你为每个类别等添加并根据该信息构建剩余的查询。



使用 CASE 当您想要将信息合并到一个列中时，可能来自不同的表，具体取决于类别



Eg

You need to drop the use of .* as well - explicitly name the columns you need.

Look for the commonality between the queries - what columns do you need in all of them, which columns are you adding for each category, etc and build up the rest of the query from that information.

Use CASE when you want to amalgamate information into a single column that might come from a different table depending on category

E.g.

SELECT CASE WHEN category = 1 Table1.Name WHEN category = 2 Table2.Name ELSE Table3.Name END As [Name]

或接受结果中的某些列可能为空白，具体取决于类别。

or accept that some columns in the results may be blank depending on category.

这篇关于我需要创建Mysql案例并基于这些案例我必须应用不同的搜索查询但我得到错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！