Common SQL usage errors in random recording work (i)

Nothing began to write a blog, leaving the previous work of common notes, incomplete content or need to add to the message, I only write my usual.

　　

There are a lot of gadgets on the web that are similar to the soft generators, although they are not soft when table relationships are complicated, but it is convenient to have some simple table-structured relationships. Think of the mistakes you made when you started working.

Now write a simple mistake, the great God may be seen at a glance, but the novice needs to understand.

Create Table Uc_counter

{

ID INT IDENTITY (1, 1) PRIMARY KEY,

MemberID int

}

　　

Create Table Uc_member

{

ID int,

Name nvarchar (50),

　　　　

}

does not discuss the design of this table is not reasonable, uc_counter MemberID is associated with the Uc_member ID

Select (select top 1 name from Uc_member a WHERE A.memberid = ID) from Uc_counter

Many code generators are similar to the SELECT * FROM Uc_counter statement when the data is isolated, they only need to enter the conditions to isolate the content (used should know, not carefully said),

The above code to find out the corresponding uc_member of the field, but will be disappointed.

The above code can be translated into

Select (select top 1 name from Uc_member a where A.memberid = a.id) from Uc_counter

So I will add a top 1, because such a subquery may be more than one data error, multiple fields the same server can not identify, looking for the most recent table field,

Correct wording: Select (select top 1 name from Uc_member a where A.memberid = b.ID) from Uc_counter b

But how can the code generator give you aliases after the table, so recommend those who want to use the Code generation tool, when the table, table relational table corresponding field name of the best different

　　

　　

Create Table Uc_counter

{

ID INT IDENTITY (1, 1) PRIMARY KEY,

MemberID int

}

　　

Create Table Uc_member

{

Mid int,

Name nvarchar (50),

　　　　

}

Select (select top 1 name from Uc_member a where A.memberid = Mid) from Uc_counter

So the use of words there is no problem, this is a sudden thought of the problem, I hope to be unfamiliar with the help of people, before purely hand-played, not enough detail to write, can leave a message.

Common SQL usage errors in random recording work (i)