MySql嵌套的select语句 [英] MySql nested select statements

问题描述

嗨

我有一个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[^]

这篇关于MySql嵌套的select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！