sql all in one for dummies

Copy CodeThe code is as follows: CREATE table TB (ID int, value varchar (10)) INSERT into TB values (1, "AA") INSERT into TB values (1, "BB") INSERT into TB values (2, "AAA") INSERT into TB values (2, "BBB") INSERT into TB values (2, "CCC") Go Create function [dbo]. [F_str] (@id int) returns nvarchar (1000) As Begin declare @str nvarchar (1000) Set @str = "" Select @str = @str + "," + cast (value as nvarchar (900)) from TB where id = @id Set @str = Right (@str, Len (@str)-1) Return @str End Go -

Tags:--fetch stat sel SQ varchar col Char where--Declaring the necessary variablesDECLARE @tablename NVARCHAR, @columnname NVARCHAR (@sql NVARCHAR), @sql2 NVARCHAR (@sql3 NVARCHAR) ( , @CName NVARCHAR (20)SET @CName = ' Regioncode '--CursorsDECLARE cur CURSOR for--1, finding a table that contains a columnSELECT ' [' +table_schema+ ']. [' +table_name+ '] ' as TableName, ' [' +column_name+ '] 'from [Information_schema]. [COLUMNS]WHERE [column_name] like

/*Department parentdept departmanage Rank--------------- -------------------- ----------------------- -----------Technical Department general manager Tom 0Program Group Technical Department GiGi 1Design Group Technical Department Yoyo 1Special Group program group Yue 2*/ --Query department manager is Gigi's superior department name; with HgO as(SELECT *,0 as rank from #EnterPrise where departmanage= ' GiGi 'UNION ALLSelect h.*,h1.rank+1 from #EnterPrise H joins HgO H1 on H.department=h1. Parent

Database environment: SQL SERVER 2005 An existing product sales Real-time table, table data as follows: The field name is the product name, the field type is the sales type, 1 is sold, 2 is the return, the field num is the quantity, and the field CTime is the operating time. Requirements: Count the sales (sale, return) of all goods within 24 hours on one line, taking into account the date. Analysis:

Tags: ---Union all usage instance SQL SELECT TOP (PERCENT) ID, bid_user_id, UserName, amount, Createtime, Borrowtype from (SE Lect bid.id, bid.bid_user_id, Reg. UserName, Bid.amount, bid.createtime, ' common standard ' as Borrowtype from dbo. Yyd_borrow_bidrecord as bid INNER JOIN dbo. Yyd_users_reginfo as Reg on reg.id = bid.bid_user_id WHERE (bid.id not in (SELECT bid from dbo. Hur_newplanloan_item

Knowledge management feedback System, analysis of Knowledge Retrieval office (Beijing Shanghai Tianjin Hong Kong New York Tokyo .... Suzhou) source, I need to find out which offices are more frequently, drain the top 5, and calculate the sum of the number of queries for all remaining departments.The database is MySQL.1 Spell out the top fiveSelect sum (countas fromgroupby5;2. Retrieve all otherTheoretically

the complexity of O (n) algorithms to ensure that the data between datasets are not duplicated. With the theoretical basis, you can change the SQL structure. After ensuring that there are no duplicates in the Data logic, you can change the two Union statements to Union all, the response speed of a query changes from 1.7 seconds to 300 milliseconds, which takes only 17% of the previous time. Union is also u

Remind me first Archives is the dedecms master table.Addonarticle news info table Run the following command in the dede background "system-" SQL command line tool "(Note: All unaudited data is deleted after running. Be careful when using this command) The Code is as follows:Delete from 'dede _ archives 'where arcrank =-1; // www.111cn.net Run phpmyadmin SQL

Tags: article immediate access Owner Personal filter exe div tinOriginal: Remove and add publications in SQL Server replication without initializing all items--If you "Prohibit schema changes" in advance, the new columns will not automatically add a large release, you should use Sp_articlecolumn to add the column exec sp_changepublication @publication = N ' publication ', @ property = N ' Replicate_ddl ', @

Tags: des style blog http color ar os sp forOriginal: "SQL" counts the rows of all tablesEnvironment:MSSQL ENT 2k8 R2principle: traverse all user tables, get the number of rows per table with the sp_spaceused process, write to the temp table, and finally return to the temporary tableIF object_id('tempdb: #TableRowCount','U') is not NULL DROP TABLE#TableRowCountG

Copy Code code as follows: --Merge duplicate rows SELECT * FROM A Union SELECT * FROM B --Do not merge duplicate rows SELECT * FROM A UNION ALL SELECT * FROM B Sort by a field --Merge duplicate rows SELECT * From ( SELECT * FROM A Union SELECT * from B) as T Order BY Field name --Do not merge duplicate rows SELECT * From ( SELECT * FROM A UNION ALL SELECT * from B) as T

Label:Any requires that your results be equal to any one.All is theCases:To pick out the order amount from the sales list is greater than "E0013 the clerk in 1996/11/10, the amount of any order received" all orders, and display the relevant field informationSelect sale_id, Tot_amtFrom saleswhere Tot_amt > any(Select Tot_amt from Saleswhere sale_id= ' e0013 'and order _date= ' 1996/11/10 ')When you change any to al