MySql嵌套的select语句

本文介绍了MySql嵌套的select语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

嗨 我有一个select语句导致c＃编码超时，也没有使用phpMyAdmin回答：

Hi I have a select statement which causes a timeout in c# coding and is also not answered using phpMyAdmin:

SELECT * FROM 'tzinterst' WHERE 'tzinterst.ZINTERNR' IN (SELECT 'tzinterl.ZINTERNR' FROM 'tzinterl' WHERE 'tzinterl.YHNUMMER' IN (SELECT 'tzstoffl.YHNUMMER' FROM 'tzstoffl' WHERE 'tzstoffl.ZNUMM' IN (SELECT 'tzspez.ZNUMM' FROM 'tzspez' WHERE 'ZLNUMM' = '008096'))) order by 'tzinterst.ZART';

执行中我收到错误 ＃1064 - 你有一个错误我们的SQL语法;查看与您的MySQL服务器版本对应的手册，以便在''tzinterst'附近使用正确的语法WHERE'tzinterst.ZINTERNR'IN（在第1行选择'tzinterl.ZINTERNR'FROM'tzi' 表格大小在10.000到20.000行之间。 我检查并重新检查表格中所有拼写和项目的存在，没有错误，我在做什么错了？ 请帮忙。 你的MiKr41 对不起，省略标记只会改变错误的类型：超时已过期。操作完成前经过的超时时间或服务器没有响应。 编码如下：

In execution I receive an error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''tzinterst' WHERE 'tzinterst.ZINTERNR' IN (SELECT 'tzinterl.ZINTERNR' FROM 'tzi' at line 1 The table sizes vary between 10.000 and 20.000 rows. I checked and rechecked all the spelling and existence of items in the tables, there is no error, what I am doing wrong? Please help. Your's MiKr41 Sorry, omitting the marks changes only the type of error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The coding looks like this:

MySqlDataAdapter DBV = new MySqlDataAdapter("SELECT * FROM tzinterst WHERE tzinterst.ZINTERNR IN " + "(SELECT tzinterl.ZINTERNR FROM tzinterl WHERE tzinterl.YHNUMMER IN " + "(SELECT tzstoffl.YHNUMMER FROM tzstoffl WHERE tzstoffl.ZNUMM IN " + "(SELECT tzspez.ZNUMM FROM tzspez WHERE ZLNUMM = '" + txt_ZLNUMM.Text + "'))) order by tzinterst.ZART", DBConn);

我的解决方法运行正常：

My workaround works fine:

MySqlDataAdapter DBspez = new MySqlDataAdapter("select ZNUMM from tzspez where ZLNUMM = '" + txt_ZLNUMM.Text + "'", DBConn); DataSet sSp = new DataSet(); if (DBspez.Fill(sSp) > 0) { sIA = new string[500]; foreach (DataRow dr in sSp.Tables[0].Rows) { MySqlDataAdapter sst = new MySqlDataAdapter("select YHNUMMER from tzstoffl where ZNUMM = '" + dr["ZNUMM"].ToString() + "'", DBConn); DataSet st = new DataSet(); if (sst.Fill(st) > 0) { foreach (DataRow dt in st.Tables[0].Rows) { MySqlDataAdapter sTd = new MySqlDataAdapter("select ZINTERNR from tzinterl where YHNUMMER = '" + dt["YHNUMMER"].ToString() + "'", DBConn); DataSet sT = new DataSet(); if (sTd.Fill(sT) > 0) { foreach (DataRow sTr in sT.Tables[0].Rows) { MySqlDataAdapter sIt = new MySqlDataAdapter("select * from tzinterst where ZINTERNR = '" + sTr["ZINTERNR"] + "' order by ZART desc", DBConn); DataSet sIts = new DataSet(); if (sIt.Fill(sIts) > 0) { bool takeIt; int i = 0; foreach (DataRow sItr in sIts.Tables[0].Rows) { takeIt = true; for (int j = 1; j < sIts.Tables[0].Rows.Count; j++) { if (sIA[j] == sItr["ZINTERGR1"].ToString()) takeIt = false; } if (takeIt) { sIA[i] = sItr["ZINTERGR1"].ToString(); string[] zin = sItr["ZTEXT"].ToString().Split((char)166); for (int k = 0; k < zin.Length; k++) rtf_Text.Text += zin[k] + "\r\n"; i++; } } } } } } } } } else MessageBox.Show("Keine Interaktionstexte gefunden!");

; 但它根本不是一个优雅的解决方案 - 对吗？ 你的MiKr41

; But it is not at all an elegant solution - right? Your's MiKr41

推荐答案

试试这个 Try this SELECT * FROM tzinterst WHERE tzinterst.ZINTERNR IN ( SELECT tzinterl.ZINTERNR FROM tzinterl WHERE tzinterl.YHNUMMER IN ( SELECT tzstoffl.YHNUMMER FROM tzstoffl WHERE tzstoffl.ZNUMM IN ( SELECT tzspez.ZNUMM FROM tzspez WHERE ZLNUMM = '008096' ) ) ) order by tzinterst.ZART;

[针对OP评论的编辑] 我上面所做的全部都是正确的语法错误。为了使其更高性能和更好阅读尝试以下（警告 - 我无法检查这一点）

All I did above was correct your syntax error. To make it more performant and read better try the following (caveat - I haven't been able to check this)

SELECT INTERST.* FROM tzspez SPEZ LEFT OUTER JOIN tzstoffl STOFF1 ON SPEZ.ZNUMM = STOFF1.ZNUMM LEFT OUTER JOIN tzinterl INTER1 ON STOFF.YHNUMMER = INTER1.YHNUMMER LEFT OUTER JOIN tzinterst INTERST ON INTER1.ZINTERNR = INTERST.ZINTERNR WHERE SPEZ.ZLNUMM = '008096' ORDER BY INTERST.ZART

其他一些需要注意的事项......你真的不应该做像

Couple of other things to note ... you really should not do things like

= '" + txt_ZLNUMM.Text + "'"

这样的事情，因为这会让你失望容易受到SQL注入攻击（并且使调试复杂的sql语句变得更加困难）。 使用参数代替 - 这是一篇从dotnetperls [ ^ ] 您可能需要使用ZNUMM，YHNUMMER等上的索引来调整数据库 - 有用免费书 - 使用卢克索引 [ ^ ]

as this leaves you vulnerable to SQL Injection attacks (and also makes it harder to debug complex sql statements). Use Parameters instead - here's an article to get you started from dotnetperls[^] You may need to tune up your database by using Indexes on ZNUMM, YHNUMMER etc - useful free book - Use the index Luke[^]