Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

SQL collection of records

D
Duquan last edited by

Hey, guys, tell me how to get a grading on the arrivals. I have two tables, one allowed tags{id,objid,name} second objects{id,title,text}; Every record allows objects to be able to be any bulb in different connections under the id. Use the request:
SELECT o . * FROM tags AS t, objects AS o WHERE (t.name LIKE '%something1%'OR t.name LIKE '%something2%' OR...OR t.name LIKE '%somethingX%')AND t.objid = o.id
but as a result, kill a similar line(s) to be duplicated and quarrel to DESC
Reply Quote 0
1 Reply Last reply

Alberto

SELECT o.id, o.title, o.text, count(*) as qty FROM tags AS t, objects AS o WHERE (t.name LIKE '%something1%'OR t.name LIKE '%something2%' OR...OR t.name LIKE '%somethingX%')AND t.objid = o.id
group by o.id, o.title, o.text
order by qty desc

http://www.sql-tutorial.ru/ru/book_group_by_clause.html

sql 22768

2
Posts

0
Views

Suggested Topics

C

How can you see how many resources the user collects in MS SQL Server 2012?
Software Programming • sql server • • Cleofas

2

0
Votes

2
Posts

0
Views

SqlServer has the following tools to monitor the state and range of productivity indicators.1. Dynamic administrative submissions and functions (sighs)Dynamic Management Views and Functions♪ https://msdn.microsoft.com/ru-ru/library/ms188754.aspx )In the first part of the question, it is not clear what indicators you are interested in. Look at this. sys.dm_os_process_memory♪ sys.dm_io_virtual_file_stats or sys.dm_os_performance_countersMaybe you can find some interesting ones.The second part of the question could be requested sys.dm_exec_sessionsselect session_id, session_duration_sec = round(cast(getdate() - login_time as float) * 86400, 3), cpu_time_sec = round(cast(cpu_time as float) / 1000, 3), reads_Mb = round(cast(reads as float) / 128, 2), writes_Mb = round(cast(writes as float) / 128, 2), logical_reads_Mb = round(cast(logical_reads as float) / 128, 2) from sys.dm_exec_sessions where original_login_name = 'LoginName' who will return the data on the current open session for the intended logic.Although, in some cases, this information is sufficient, it is clear, however, that it is not possible to obtain information on the final sessions. I mean, in a way, it's "mean" figures.If interest is not instantaneous and dynamics, it can be traced to polling. Such a decision, however, may not be appropriate in the case of, for example, short-lived sessions. Better use the means of tracking events.2. Traces (sighs)SQL Trace♪ https://msdn.microsoft.com/ru-ru/library/hh245121.aspx )To date, this is a worker, but outdated, so I will limit myself to mentioning it. He was replaced. Expanded developments♪3. Expanded developments (sighs)Extended Events♪ https://msdn.microsoft.com/ru-ru/library/bb630282.aspx )This tool is less resource-neutral compared to the transhipment (but it is not well-used, and can be said to be a kneeling server).Event data can be collected not only in the file, but also in the simple. Computer or Gestogram (which is very cost-effective in terms of memory costs) ring buffer (for cyclical events). It is possible to establish an automatic launch of a session of expanded events at the beginning of the SqlServer.In the second part of the question, for example, it is possible to trace events logout (relates to the package sqlserverwho has fieldsdurationcpu_timephysical_readslogical_readswritesContaining the accumulated values during the session by adding a filter sqlserver.session_server_principal_name = N'LoginName'♪For example:CREATE EVENT SESSION [LoginName_resource_usage] ON SERVER ADD EVENT sqlserver.logout ( WHERE ([sqlserver].[session_server_principal_name] = N'LoginName') ) ADD TARGET package0.event_file ( SET filename = N'd:\Temp\XE_Logs\LoginName_resource_usage.xel', max_file_size = 50, max_rollover_files = 0 ) WITH ( MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 30 SECONDS, MAX_EVENT_SIZE = 0 KB, MEMORY_PARTITION_MODE = NONE, TRACK_CAUSALITY = OFF, STARTUP_STATE = OFF) GO ALTER EVENT SESSION [LoginName_resource_usage] ON SERVER STATE = START GO After which events are read (with help) sys.fn_xe_file_target_read_fileand analysed (e.g., summarised by time intervals, etc.).
N

Change query sql
Software Programming • php mysql sql performance • • nishika

2

0
Votes

2
Posts

0
Views

E

Creation of Procedure(is the example of how to create the procedure is not yet completed):DELIMITER $$ DROP PROCEDURE IF EXISTS promedio $$ CREATE PROCEDURE promedio(in dateStart timestamp,in dateFinal timestamp,in idTenant int) BEGIN DECLARE cont int default 0; DECLARE dateProcesada timestamp; /primer lunes/ SET dateProcesada= DATE_ADD(dateStart, INTERVAL - WEEKDAY(dateStart) DAY); /tendrias que verificar bien el tipo de datos para las columnas de la tabla temporal/ CREATE TEMPORARY TABLE promedio_temp ( product int, Week varchar(10), PlantSowing bigint, production int, PRIMARY KEY(product) ); WHILE dateProcesada < dateFinal DO /misma consulta la idea es crear una tabla temporal para sw_sowing de forma tal q el max se calcule en este punto y devolver los datos en esa temporal/ INSERT INTO promedio_temp(product,Week,PlantSowing,production) SELECT pr_products.product, CONCAT(YEAR(dateProcesada),'-',LPAD(WEEK(dateProcesada),2,'0')) AS Week, SUM(IF(sw_sowing.type = 'SW', sw_sowing.quantity,0)) AS PlantSowing, SUM(IF(ROUND(DATEDIFF(TIMESTAMPADD(DAY,(6- WEEKDAY(dateProcesada)),dateProcesada), sw_sowing.date)/7) >= pr_products.week_production AND sw_sowing.type = 'SW',sw_sowing.quantity,0)) AS production FROM ( SELECT max(sw_sowing.id) AS id FROM sw_sowing WHERE sw_sowing.status != 0 AND sw_sowing.id_tenant=idTenant AND sw_sowing.status = 100 AND sw_sowing.date <= TIMESTAMPADD(DAY,(6-WEEKDAY(dateProcesada)),dateProcesada) GROUP BY sw_sowing.id_production_unit_detail ) AS sw INNER JOIN sw_sowing ON sw_sowing.id = sw.id INNER JOIN pr_products ON pr_products.id = sw_sowing.id_product INNER JOIN pr_varieties ON sw_sowing.id_variety = pr_varieties.id INNER JOIN pr_lands ON pr_lands.id = sw_sowing.id_land WHERE pr_varieties.code != 1 AND sw_sowing.id_product = 1 AND sw_sowing.status = 100 GROUP BY pr_products.product HAVING plantSowing > 0 ORDER BY pr_products.product; /*sumar 7 dias para obtener proximo lunes*/ SET dateProcesada= DATE_ADD(dateProcesada, INTERVAL 7 DAY); end while; SELECT product,Week,PlantSowing,production FROM promedio_temp; END $$ DELIMITER ; To call her use this:$sql="call promedio('$firstDate',''$lastDate',:id_intent)";
S

Data downloading problem with MSSQL 2017 from Postgressql server
Software Programming • sql server sql postgresql • • shizuka

2

0
Votes

2
Posts

0
Views

Z

When faced with this problem, a clear indication of data types was helpful select top 1 * from openquery([PG], ' SELECT * FROM ( SELECT date, cast(login as nchar(10)), cast(request as nchar(...) ) AS "Request", cast(response as nchar(...)) AS "Response", cast(exc as nchar(...)) AS "Exception" FROM logs ) AS result WHERE date >= ''20190416'' ORDER BY date DESC ')
M

SQL showing records if they only meet different conditions of the usual
Software Programming • sql server sql postgresql databank firebird • • meriah

2

0
Votes

2
Posts

0
Views

G

Using clause IN:SELECT * FROM AUTOR WHERE ID IN (SELECT ID_AUTOR FROM LIVROS GROUP BY ID_AUTOR HAVING MIN(ANO) >= 1919);
Multiple SQL SELECT
Software Programming • sql select • • Demir

2

0
Votes

2
Posts

0
Views

A

The problem is that you probably don't have the standardized tables and have a lot of redundancy thus generating many repeated fields. It is normal that the fields come out often _mod as they will come out as many times as dates from the other fields, those that if they can go several times. If you do a GROUP BY, you will surely lose information.
K

How do you find the Id line number after the grading?
Software Programming • sql server sql • • Kadyn

2

0
Votes

2
Posts

0
Views

P

Use it. https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017 select id, col1, row_number() over(order by col1 desc) as row_num from #table with NumberedRows as ( select id, col1, row_number() over(order by col1 desc) as row_num from #table ) select id, col1, row_num from NumberedRows where col1 = 'jkl'
K

SQL query only for non-nulos records
Software Programming • sql • • kalyn

2

0
Votes

2
Posts

0
Views

G

Null is not a value, need to use IS NULL to find null or IS NOT NULL to not null at the time of comparison. Something like:SELECT * FROM NotasFiscais WHERE NChave IS NOT NULL Recommended reading: https://pt.stackoverflow.com/q/90363/91
How to search for a column data in another column in SAS using Proc-SQL?
Software Programming • sql server sql sas • • Alberto

2

0
Votes

2
Posts

0
Views

O

If the missing digits are the 2 on the right, try using the Left function to get only the 12 digits on the left:ON A.CPF_CLIENTE = LEFT(B.NUM_CPF_CNPJ_CLI, 12) I hope it helps
J

Record
Software Programming • c sharp • • jonetta

2

0
Votes

2
Posts

0
Views

T

It's a free translation and compilation of. https://stackoverflow.com/questions/4878012/reading-guitar-pro-files for reference proposed in @maxwell.There's a Java application with an open source code... http://tuxguitar.herac.com.ar/ You can see his source.Also. http://dguitar.sourceforge.net/GP4format.html received by reversing engineering. It is also possible to look at the originator of the DGuitar itself, which represents a plug.♪ GP4,♪ GP3,GTP). The truth is, that's what Java's supposed to understand, because this player is written on it.You can also look at the source. http://kguitar.sourceforge.net/ , it's also a plug but c++ and qt.On c# http://phoneguitartab.codeplex.com/ Looker gtp for Windows Phone https://github.com/reinterpretcat/phoneguitartab )
B

XML data collection and collection
Software Programming • jquery ajax xml xmlparser • • Burma

2

0
Votes

2
Posts

0
Views

A

function xmlParser(xml) { var summ = 0; $(xml).find("stat>advcampaign:contains(15454)").each(function () { payment = +$(this).siblings("payment").text(); summ += payment; } );
D

Get a sql connection using entityManager
Software Programming • java sql weblogic language • • derk

2

0
Votes

2
Posts

0
Views

T

If you use EclipseLink, and according to the JPA version:JPA 2.0 entityManager.getTransaction().begin(); java.sql.Connection connection = entityManager.unwrap(java.sql.Connection.class); // unwraps the Connection class. ... entityManager.getTransaction().commit(); JPA 1.0entityManager.getTransaction().begin(); UnitOfWork unitOfWork = (UnitOfWork)((JpaEntityManager)entityManager.getDelegate()).getActiveSession(); unitOfWork.beginEarlyTransaction(); Accessor accessor = unitOfWork.getAccessor(); accessor.incrementCallCount(unitOfWork.getParent()); accessor.decrementCallCount(); java.sql.Connection connection = accessor.getConnection(); ... entityManager.getTransaction().commit(); Out of a https://stackoverflow.com/questions/6707115/get-hold-of-a-jdbc-connection-object-from-a-stateless-bean that in turn reference to http://wiki.eclipse.org/EclipseLink/Examples/JPA/EMAPI#Getting_a_JDBC_Connection_from_an_EntityManager .Adding to what commented by Luggi Mendoza, an example of the new way to call PLs, JPA 2.1 forward:StoredProcedureQuery query = this.em.createStoredProcedureQuery("nombreProcedimiento"); query.registerStoredProcedureParameter("paramEntrada1", Double.class, ParameterMode.IN); query.registerStoredProcedureParameter("paramEntrada2", Double.class, ParameterMode.IN); query.registerStoredProcedureParameter("paramSalida1", Double.class, ParameterMode.OUT); query.setParameter("paramEntrada1", 1.23d); query.setParameter("paramEntrada2", 4.56d); query.execute(); Double sum = (Double) query.getOutputParameterValue("paramSalida1");
K

SQL update field F with date of first record containing field P  0
Software Programming • sql update • • kalena

2

0
Votes

2
Posts

0
Views

E

Attached scriptDROP TABLE Prueba CREATE TABLE Prueba ( FechaA DATE NOT NULL, FechaB DATE NULL, Nombre VARCHAR(100) NOT NULL, P INT NOT NULL ) INSERT INTO Prueba VALUES ('20170506', NULL, 'Juan', -1) INSERT INTO Prueba VALUES ('20180102', NULL, 'Fer', 9) INSERT INTO Prueba VALUES ('20190506', NULL, 'Vale', -1) INSERT INTO Prueba VALUES ('20190806', NULL, 'Vivi', -1) INSERT INTO Prueba VALUES ('20190506', NULL, 'Rosa', -1) INSERT INTO Prueba VALUES ('20180102', NULL, 'Juan', 2) INSERT INTO Prueba VALUES ('20190506', NULL, 'Vivi', 1) INSERT INTO Prueba VALUES ('20190506', NULL, 'Juan', 6) INSERT INTO Prueba VALUES ('20190605', NULL, 'Vivi', 6) SELECT * FROM Prueba A UPDATE A SET A.FechaB = B.FechaA FROM Prueba A INNER JOIN ( SELECT * FROM ( SELECT FechaA, FechaB, Nombre, P, ROW_NUMBER() OVER(PARTITION BY Nombre ORDER BY Nombre, P ASC) AS ROWORDER FROM Prueba WHERE P > 0 ) TEMP WHERE ROWORDER = 1 ) B ON A.Nombre = B.Nombre SELECT * FROM Prueba A The secret is in ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME, P ASC) AS ROWORDER, Group and makes an increase between each group, of which then we only select the first.This is the result I get after the modificationFechaA FechaB Nombre P 2017-05-06 2018-01-02 Juan -1 2018-01-02 2018-01-02 Fer 9 2019-05-06 NULL Vale -1 2019-08-06 2019-05-06 Vivi -1 2019-05-06 NULL Rosa -1 2018-01-02 2018-01-02 Juan 2 2019-05-06 2019-05-06 Vivi 1 2019-05-06 2018-01-02 Juan 6 2019-06-05 2019-05-06 Vivi 6 Waiting for your answer.
G

How to merge the tables into one
Software Programming • sql • • gionna

2

0
Votes

2
Posts

0
Views

A

SELECT q.point, q.date, t1.inc, t2.out FROM (SELECT point, date FROM Income UNION SELECT point, date FROM Outcome ) q LEFT JOIN Income t1 ON t1.point=q.point AND t1.date=q.date LEFT JOIN Outcome t2 ON t2.point=q.point AND t2.date=q.date Or if the dialect allowsSELECT COALESCE (t1.point, t2.point) point, COALESCE (t1.date, t2.date) date, t1.inc, t2.out FROM Income t1 FULL JOIN Outcome t2 ON t1.point=t2.point AND t1.date=t2.date
J

Insert related table record from c# to sql server
Software Programming • c sharp sql server • • jonetta

2

0
Votes

2
Posts

0
Views

P

The first thing I would advise is that you use parameters, attach in a string the values of insert not recommendedThe code should look like thispublic void Registrar() { using (SqlConnection conn = new SqlConnection("connection string")) { string sql = @"Insert into Persona (Nombre, ApPaterno, ApMaterno, FechaNac, Documento, Direccion, Telefono, Sexo, Email) values(@nombre, @ApPaterno, @ApMaterno, ...)"; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.AddWithValue("@nombre", this.Nombre); cmd.Parameters.AddWithValue("@ApPaterno", this.ApPaterno); cmd.Parameters.AddWithValue("@ApMaterno", this.ApMaterno); . . cmd.ExecuteNonQuery(); } } Use SIEMPRE parameters to assign the values.Due to the problem of inheritance analyzes this article http://ltuttini.blogspot.com/2013/03/n-layer-schoolmanager-herencia-y.html See how I define an example using Instructor that inherits from Persona then from the Save() method is first called when recording the base class (i.e. person) is inserted and then proceeds with the instructor. By inserting the person you get the Id to be able to link the records between the tables.
N

optimize reading and inserting of .txt to sql records in Java
Software Programming • java sql netbeans • • Nykeriab

2

0
Votes

2
Posts

0
Views

E

As it seems, what slows your code most is the execution of an insert per line, which results in 15 million file-executed inserts. This could be arranged if instead of making an insert per line, insert several values into the same insert, such as, for example, the following:BufferedReader br = new BufferedReader(new FileReader(filename)); String line; StringBuffer sb = new StringBuffer(); Date hoy = new Date(); String hoyString = new SimpleDateFormat("yyyy-MM-dd").format(hoy); int VALORES_POR_INSERT=2000; List<String> lInserts = new ArrayList<>(15000000/VALORES_POR_INSERT); //Asi no tiene que estar creciendo mucho sb.append("INSERT INTO A(id, fecha) VALUES "); int iteracion = 0; while((line = br.readLine()) != null) { String[] split = line.split("\|"); sb.append("('") .append(split[0]) .append("', '") .append(hoyString) .append("'),"); ++iteracion; if(iteracion == VALORES_POR_INSERT) { iteracion = 0; sb.deleteCharAt(sb.length()-1); //Quitamos la ultima "," lInserts.add(sb.toString()); sb.setLength(0); sb.append("INSERT INTO A(id, fecha) VALUES "); } } I have generated a 15 million-line file, and this algorithm (which can surely be improved) takes about 5 seconds (or what is the same, a temporary improvement of 17000% approximately) read it. Using a variation of this, and running queries at the end of the reading, you should dramatically improve your performance. Another thing to try with different values for N_LINEAS that in the end is the one who says how many lines per insert we have. He's in. 2000, but surely you can increase enough, and therefore improve performance.PD: In this code, I used the array lInsertsas if it were stmt.addBatch(query); I mean, an array of queries that will then be executed. We have to make the necessary changes to get him to marry your problem.IMPORTANT This code presupposes that you can read from line 0 of the file, make the necessary changes for your own configuration.

SQL collection of records

Suggested Topics

How can you see how many resources the user collects in MS SQL Server 2012? Software Programming • sql server • • Cleofas

Change query sql Software Programming • php mysql sql performance • • nishika

Data downloading problem with MSSQL 2017 from Postgressql server Software Programming • sql server sql postgresql • • shizuka

SQL showing records if they only meet different conditions of the usual Software Programming • sql server sql postgresql databank firebird • • meriah

Multiple SQL SELECT Software Programming • sql select • • Demir

How do you find the Id line number after the grading? Software Programming • sql server sql • • Kadyn

SQL query only for non-nulos records Software Programming • sql • • kalyn

How to search for a column data in another column in SAS using Proc-SQL? Software Programming • sql server sql sas • • Alberto

Record Software Programming • c sharp • • jonetta

XML data collection and collection Software Programming • jquery ajax xml xmlparser • • Burma

Get a sql connection using entityManager Software Programming • java sql weblogic language • • derk

SQL update field F with date of first record containing field P  0 Software Programming • sql update • • kalena

How to merge the tables into one Software Programming • sql • • gionna

Insert related table record from c# to sql server Software Programming • c sharp sql server • • jonetta

optimize reading and inserting of .txt to sql records in Java Software Programming • java sql netbeans • • Nykeriab

How can you see how many resources the user collects in MS SQL Server 2012?
Software Programming • sql server • • Cleofas

Change query sql
Software Programming • php mysql sql performance • • nishika

Data downloading problem with MSSQL 2017 from Postgressql server
Software Programming • sql server sql postgresql • • shizuka

SQL showing records if they only meet different conditions of the usual
Software Programming • sql server sql postgresql databank firebird • • meriah

Multiple SQL SELECT
Software Programming • sql select • • Demir

How do you find the Id line number after the grading?
Software Programming • sql server sql • • Kadyn

SQL query only for non-nulos records
Software Programming • sql • • kalyn

How to search for a column data in another column in SAS using Proc-SQL?
Software Programming • sql server sql sas • • Alberto

Record
Software Programming • c sharp • • jonetta

XML data collection and collection
Software Programming • jquery ajax xml xmlparser • • Burma

Get a sql connection using entityManager
Software Programming • java sql weblogic language • • derk

SQL update field F with date of first record containing field P  0
Software Programming • sql update • • kalena

How to merge the tables into one
Software Programming • sql • • gionna

Insert related table record from c# to sql server
Software Programming • c sharp sql server • • jonetta

optimize reading and inserting of .txt to sql records in Java
Software Programming • java sql netbeans • • Nykeriab